February 10, 2010 at 12:33 am
If you have a 1->M table relationship, between tblMaster and tblChild table, how can you best write a stored proc that will move those records to another set of tables like prod.tblMaster and prod.tblChild
The solution I have right now is to cursor through the master table, do an insert to the new table and get the identity key, and then do a insert into the child prod.tblChild table
I would like to change this is possible - I can have a large number of records in the tblChild tables and the cursor method is really slow.
Thanks!
February 10, 2010 at 2:09 am
You could simply run:
INSERT INTO prob.tblMaster SELECT * FROM tblMaster
INSERT INTO prob.tblChild SELECT * FROM tblChild
February 10, 2010 at 4:35 am
Sorry, I didn't notice that the connection was based on an identity column.
In this case try the following:
ALTER TABLE prod.tblMaster ADD PrevID BIGINT -- Or whatever
INSERT INTO
prod.tblMaster
SELECT
tblMaster.Column1,
tblMaster.Column2,
... -- All columns but ID
tblMaster.ID AS PrevID FROM tblMaster
FROM
tblMaster
INSERT INTO
prob.tblChild
SELECT
tblChild.Column1,
tblChild.Column2,
... -- All except ID,
prob.tblMaster.ID AS ID
FROM
tblChild
INNER JOIN prob.tblMaster.ID
ON tblChild.ID = prob.tblMaster.PrevID
ALTER TABLE prod.tblMaster DROP COLUMN PrevID
February 10, 2010 at 5:40 am
You could try using the Switch command.
ALTER TABLE [dbo].tbl1 SWITCH to [dbo].tbl2
February 10, 2010 at 5:52 am
Another option is to use the OUTPUT clause to capture the generated ID's and then use them in the inserts in the second table.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 10, 2010 at 5:53 am
sql_lock (2/10/2010)
You could try using the Switch command.
ALTER TABLE [dbo].tbl1 SWITCH to [dbo].tbl2
Don't you also have to set up partitioning between these tables first? I'm asking because I've never used the SWITCH function.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 10, 2010 at 6:22 am
A modified version after Willem's note (thanks Willem):
INSERT INTO
prod.tblMaster
OUTPUT
INSERTED.ID AS ID,
tblMaster.ID AS PrevID
INTO #t
SELECT
*
FROM
tblMaster
INSERT INTO
prob.tblChild
SELECT
tblChild.Column1,
tblChild.Column2,
... -- All except ID,
#t.ID AS ID
FROM
tblChild
INNER JOIN #t
ON tblChild.ID = #t.PrevID
Hope it runs
February 10, 2010 at 6:22 am
Grant,
In my example, no (as sql is just changes the pointers to the meta-data). But to answer with the original post in mind then yes you would need to setup some pre-req's 1st.
i.e
Same filegroup must be used, partition must exist etc etc
The Article on MSDN give you futher details..
February 10, 2010 at 6:26 am
sql_lock (2/10/2010)
Grant,In my example, no (as sql is just changes the pointers to the meta-data). But to answer with the original post in mind then yes you would need to setup some pre-req's 1st.
i.e
Same filegroup must be used, partition must exist etc etc
The Article on MSDN give you futher details..
Good article and a neat trick. Thanks.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 10, 2010 at 6:45 am
February 10, 2010 at 6:50 am
wschampheleer (2/10/2010)
pink_panther,Thank you for the compliment.
Unfortunately, I believe your new version is no improvement: you still need to insert the old ID if you want to retrieve it using the OUTPUT option...
No, the OUTPUT function will get the ID generated by the insert. That's the biggest beauty of it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 10, 2010 at 6:55 am
Grant Fritchey (2/10/2010)
wschampheleer (2/10/2010)
pink_panther,Thank you for the compliment.
Unfortunately, I believe your new version is no improvement: you still need to insert the old ID if you want to retrieve it using the OUTPUT option...
No, the OUTPUT function will get the ID generated by the insert. That's the biggest beauty of it.
Grant,
I am familiar with the OUTPUT clause. however, the problem that I see is that you need to be able to relate the generated ID to the ID in the old table so you can join it to the old details table. I have the impression that this is not going to work in pink_panther's solution.
February 10, 2010 at 7:07 am
wschampheleer (2/10/2010)
Grant Fritchey (2/10/2010)
wschampheleer (2/10/2010)
pink_panther,Thank you for the compliment.
Unfortunately, I believe your new version is no improvement: you still need to insert the old ID if you want to retrieve it using the OUTPUT option...
No, the OUTPUT function will get the ID generated by the insert. That's the biggest beauty of it.
Grant,
I am familiar with the OUTPUT clause. however, the problem that I see is that you need to be able to relate the generated ID to the ID in the old table so you can join it to the old details table. I have the impression that this is not going to work in pink_panther's solution.
Possibly true. I'm making the (probably erroneous) assumption that there is a natural key to allow for identifying the values in addition to the ID.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 10, 2010 at 8:00 am
Thank you all for some great ideas. The output clause is soooo close to what I need, but not quite. Of the solutions offered:
1) I can't use the switch because I am appending records from my source table to my destination table, not moving an entire table. A switch would wipe out my old records in my destination table
2) The ouptut option as described by pink_panther has the flaw that the output parameters on the insert only sees the columns being inserted. Thus the need to have a place to stick the prevID during the insert. That is why the other solution had an Alter Table to add the prevID. So, I either have to Alter the table everytime I append something or always have the previousID as a column in my tables. Since I can't be altering a particularly large table and incurring the locks on my prod tables, that leaves me with changing my standard to have a prevID defined in my tables that I use solely for copying records.
Unless there are any other ideas, I may have to either stick to a cursor for this type of project or re-architect many of my tables....
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply