How to structure a nested loop/cursor

[travisbrown]

I have a question about how to approach this. I need to generate some insert scripts to insert some data that was accidentally inputted into the wrong database into another unlinked db. I need to append it and allow the destination db to generate new keys. I can generate the SQL fine, but I'm stumped how to handle the two foreign key tables. I didn't get far with a loop. Is this something I need to use a cursor for?

Here is the code to write the parent table SQL.

SELECT 'INSERT INTO dbo.tblDispatchTicket 
(ticket_number, ticket_district, ticket_emergent, ticket_start_ts, ticket_end_ts, ticket_user, ticket_notes, ticket_status, ticket_called_manager, ticket_followupnotes)
VALUES 
(''' +
ticket_number + ''','''+
CONVERT(varchar(4),ticket_district)+''','+
CONVERT(char(1),ticket_emergent)+','''+
CONVERT(varchar(50),ticket_start_ts)+''','''+
CONVERT(varchar(50),isNull(ticket_end_ts,DateAdd(hh,1,ticket_start_ts)))+''','''+
CONVERT(varchar(50),ticket_user)+''','''+
isNull(RTRIM(LTRIM(REPLACE(ticket_notes,'''',''''))),'')+''','''+
CONVERT(varchar(50),ticket_status)+''','+
CONVERT(varchar(50),isNull(ticket_called_manager,0))+','''+
isNull(RTRIM(LTRIM(ticket_followupnotes)),'') + ''')'
FROM dbo.tblDispatchTicket
WHERE     (ticket_id >= 146)

Then there are two child tables that I have to query, using ticket_id as the key. For each record above, I need to return all related rows and insert then using Scope_Identity() for each record. Structurally I guess it would have to loop like this, but I don't know how to pull parent record, call all child 1 and then all child 2, then move to next record:

Parent insert 1
child1 insert a
child1 insert b
child2 insert a
child2 insert b
child2 insert c
Parent insert 2
...

here are the other two inserts. I'd have to replace tic_fid and ticket_fid with the scope_identity of the parent. How should I structure this?

SELECT 'INSERT INTO  dbo.tblDispatchTicketResponses  
(tic_fid, c_fid, res_response, res_ts, res_notes)
VALUES ('+ 
CONVERT(varchar(4),tic_fid)+','+
CONVERT(varchar(4),c_fid)+','''+
res_response+''','''+
CONVERT(varchar(20),res_ts)+''','''+
isNull(RTRIM(LTRIM(res_notes)),'')+
''')'
FROM         dbo.tblDispatchTicketResponses
WHERE     (tic_fid >= 146)


SELECT 'INSERT INTO dbo.tblDispatchTicketContactNote 
(ticket_fid, c_fid, dc_note, dc_ts)
VALUES
('+
CONVERT(varchar(4),ticket_fid)+','+
CONVERT(varchar(4),c_fid)+','''+ 
RTRIM(LTRIM(dc_note))+''','''+ 
CONVERT(varchar(20),RTRIM(LTRIM(dc_ts)))+''')'
FROM         dbo.tblDispatchTicketContactNotes
WHERE     (ticket_fid >= 146)

 

[travisbrown]

~bump~

 

[zhavic]

You should create temporary column in your parent table to hold original ticked_id and after all, drop that column, e.g.:

In the table dbo.tblDispatchTicket create one more column called previous_ticked_id

Than do your insert into that table

INSERT INTO dbo.tblDispatchTicket
(all your columns, previous_ticked_id )
VALUES( all your values, ticked_id )
FROM dbo.tblDispatchTicket
WHERE (ticket_id >= 146)

Than you can simple insert into child tables
( you need to add aliases before column names if parent and child tables has the same colum names )

SELECT 'INSERT INTO dbo.tblDispatchTicketResponses
(tic_fid, c_fid, res_response, res_ts, res_notes)
VALUES
(' +
CONVERT(varchar(4),tblDispatchTicket.ticked_id) + ','+
CONVERT(varchar(4),c_fid)+','''+
res_response+''','''+
CONVERT(varchar(20),res_ts)+''','''+
isNull(RTRIM(LTRIM(res_notes)),'')+
''')'
FROM dbo.tblDispatchTicketResponses
INNER JOIN dbo.tblDispatchTicket ON tblDispatchTicket.previous_ticked_id = tblDispatchTicketResponses.tic_fid
WHERE (tic_fid >= 146)


SELECT 'INSERT INTO dbo.tblDispatchTicketContactNote
(ticket_fid, c_fid, dc_note, dc_ts)
VALUES
(' +
CONVERT(varchar(4),tblDispatchTicket.ticked_id) +','+
CONVERT(varchar(4),c_fid)+','''+
RTRIM(LTRIM(dc_note))+''','''+
CONVERT(varchar(20),RTRIM(LTRIM(dc_ts)))+''')'
FROM dbo.tblDispatchTicketContactNotes
INNER JOIN dbo.tblDispatchTicket ON tblDispatchTicket.previous_ticked_id = tblDispatchTicketContactNotes.ticked_fid
WHERE (ticket_fid >= 146)

Than drop the column previous_ticked_id from dbo.tblDispatchTicket

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.