Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Concatenate string values to another table 1

Status
Not open for further replies.
petermeachem

petermeachem

Programmer
Aug 26, 2000
2,270
0
0
GB
I've got two tables. DeliveryNotes contains OrderEntryID and DeliveryNote, and Orders also contains OrderEntryId and DisplayDeliveryNotes (it's a temp table). OrderEntryId is unique in Orders, in the other table there can be around 5 to 10 DeliveryNote per OrderEntryId. I need to string together the DeliveryNote from DeliveryNotes and put them into DisplayDeliveryNotes in Orders against the relevant OrderEntryId. It's being done as a recordset loop in vb at the moment which works ok, but takes too long and dumps a lot of data across the network.
I can't figure out how to do this as a stored procedure. Could someone point me in the right direction please.
Example
DeliveryNotes
1 Del1a
2 Del2a
2 Del2b
4 Del4a

I need to end up in Orders with

1 Del1a
2 Del2a , Del2b
3
4 Del4a

 
Sort by date Sort by votes
Well, this is actually a place to break the rules (my rule anyway...a view not held by all). This is where a trigger makes sense. There is one thing that makes me hesitate. You say that it's a temp table. Does it refer to DeliveryNotes? Why is that?
The way to get that string is as follows:
Code: 
Declare @DeliveryString as varchar(1000)
Select @DeliveryString=Coalese(@DeliveryString+', ','')+DisplayDeliveryNote from DeliveryNotes where OrderEntryID=@OrderEntryID
I didn't define @OrderEntryID because it actually should come from either the Update or Insert Trigger of the DeliveryNotes table. Frankly, I don't know the trigger syntax, because I never use them! But if someone else doesn't post it, I'll look it up tomorrow.
Let me know about that temp table.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Upvote 0 Downvote
Hi,

You have to use a cursor to do what you want to do it. If you don't know the code to a cursor then please reply I can write a sample code for you.

Thanks,
 
Upvote 0 Downvote
You don't have to use a cursor. The code I gave you will do it. If the table is truncated or the old orders are deleted, you can still use a trigger on the DeliveryNotes table. You should read about triggers in BOL and I'll have a go at it tomorrow.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
I came up with the code below by reading tek-tips most of the morning. It's a bit slow (at least on what I refer to as my server), but cuts down the amount of data punted about on the network. the customer is using a slow wan, so this is quite important.


if exists (select * from sysobjects where name = 'InsertDeliveryNotes' AND Type = 'P')
Drop Procedure InsertDeliveryNotes
GO

CREATE PROC InsertDeliveryNotes AS


Create Table #TempDelivNotes(
IDTempDelivNotes int NOT NULL Identity,
OrderEntryId int,
DeliveryNote VarChar(3000)
)



Insert into #TempDelivNotes (OrderEntryId)
SELECT DISTINCT
OrderEntryId
FROM
#TempRunSheet


declare @DeliveryNote nvarchar(1000)
declare @iRow int
declare @iMax int
Select @iMax = count(OrderEntryId) from #TempDelivNotes
Select @iRow = 1
while @iRow <= @iMax

BEGIN


Select @DeliveryNote = ' '
SELECT @DeliveryNote = Coalesce(@DeliveryNote + ', ', '') +
DeliveryNote FROM Delivery
WHERE
OrderEntryId = (SELECT OrderEntryId FROM #TempDelivNotes WHERE IDTempDelivNotes = @iRow)


Update #TempDelivNotes SET DeliveryNote = @DeliveryNote
WHERE IDTempDelivNotes = @iRow

Select @iRow = @iRow + 1
END


UPDATE #Computerunsheets SET #Computerunsheets.DeliveryNote = #TempDelivNotes.DeliveryNote
FROM #Computerunsheets,#TempDelivNotes
WHERE #TempDelivNotes.OrderEntryId = #Computerunsheets.OrderEntryId

SELECT * FROM #TempDelivNotes

DROP Table #TempDelivNotes

 
Upvote 0 Downvote
Peter, it's a good way to avoid a cursor, but the problem is that it uses cursor logic (row by row manipulation) rather than set logic. You can get the same result with set logic and it will run much faster. But a trigger would be even better.
I don't have a grasp of the big picture. Is the objective to create a recordset that can be sent back to the client for a report? The last select of your SP appears to do that, but don't you want the DisplayNotes in (or joined to) the Orders table?
This is ironic. I normally lobby against triggers and now I find myself trying to sell one!
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Good morning (or afternoon).

I'm not sure why 'SELECT * FROM #TempDelivNotes' is in there, forgot to delete it. The report is done with #Computerunsheets after a bit more fiddling about. I don't see how to do it with set logic at all.

Triggers are only second on your hate list after all.

I've sent the customer a copy to try. It coule be ok as their server is stonkingly fast, but the depot comms links are very slow. This, plus other changes has reduced the time by a third, the procedure taking up most of what is left and network traffic is down by 90%.


 
Upvote 0 Downvote
What about #computerunsheets, why is that a temporary table? Where is it defined?
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
How can it be used in InsertDeliveryNotes? I don't understand how that Update to #computerunsheets work?
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Upvote 0 Downvote
Papps is again all-at-sea when facing Saggers, who probes outside off stump.

And also

Papps edges Saggers well in front of the slip cordon, although the crowd gets excited.

And

Styris gets his innings underway flicking a full delivery from Saggers through square leg.

Good game.

 
Upvote 0 Downvote
This part of the code can do nothing since #computerunsheets isn't previously defined.
Code: 
UPDATE #Computerunsheets   SET #Computerunsheets.DeliveryNote = #TempDelivNotes.DeliveryNote
FROM #Computerunsheets,#TempDelivNotes 
WHERE #TempDelivNotes.OrderEntryId = #Computerunsheets.OrderEntryId
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
I'm astounded! So VB6 (I assume) creates a temporary table (how does it do that ... SQL or a SP) and you run InsertDeliveryNotes to modify that table.
Ok, I except that on faith while I'm mystified.
Code: 
CREATE PROC InsertDeliveryNotes2 
AS
UPDATE #Computerunsheets   
   SET DeliveryNote = (Coalesce(#Computerunsheets.DeliveryNote  + ', ', '') +DeliveryNote
   FROM Delivery
   WHERE OrderEntryId = #Computerunsheets.OrderEntryID)
Try that. I'm not certain that it will work. It would be best if #Computerunsheets.DeliveryNote is null rather than just blank to start with. BTW, your code will insert a leading ', ' because of
Code: 
Select @DeliveryNote = ' '
take that out and you shouldn't get a leading ', '. That's why it's best to start with a Null. I'm assuming that you don't need to create those other temporary tables for some other report, but I'm not certain of anything now that you tell me that temporary tables don't "go out of scope" from within the VB application in which they were created.
-Karl



[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
I think "except" was a Freudian slip.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
168
BobSuruncle
BobSuruncle
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
105
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
113
Cpreston
Cpreston
Cpreston
  • Question
Seconds to minutes 2
Replies
5
Views
272
Chris Miller
Chris Miller
eboughey1008
  • Locked
  • Question
Import columns from one table into another table with dissimilar field layout
Replies
7
Views
103
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top