Copying & Tracking Data

Question

Post reply

Copying & Tracking Data

Goldie Lesser

SSCertifiable

Points: 6155
More actions
August 6, 2009 at 3:21 pm

#203540

I am trying to copy sections for a specified test, and I need to track the mapping between new and old section ids.
I can't figure out how to make it work.
-- some DDL and sample DATA
DECLARE @test-2 TABLE
(
ID INT IDENTITY(1,1)
,Name VARCHAR(20)
)
DECLARE @Sections TABLE
(
ID INT IDENTITY(1,1)
,Name VARCHAR(20)
,Property INT
,TestID INT
,Sequence INT
)
-- create some tests
INSERT INTO @test-2
SELECT 'Test 1'
UNION ALL SELECT 'Test 2'
-- create some sections for both tests
INSERT INTO @Sections (TestID, Name, Property, Sequence)
SELECT 1, 'Short Answer', 54, 1
UNION ALL SELECT 1, 'Essay', 82, 2
UNION ALL SELECT 1, 'M/C', 93, 3
UNION ALL SELECT 1, 'Short Answer', 61, 4
UNION ALL SELECT 2, 'Essay', 38, 1
UNION ALL SELECT 2, 'T/F', 71, 2
-- view data
SELECT *
FROM @test-2
SELECT *
FROM @Sections
-- goal is to copy over data for specified test
-- need to keep track of which sections map to each other
-- here's the insert
-- but how can i keep track of the mapping?
INSERT INTO @Sections(TestID, Name, Property, Sequence)
SELECT TestID
,Name
,Property
,Sequence
FROM @Sections
WHERE TestID = 1
SELECT *
FROM @Sections
I am looking for something like this for results:
OldSectionIDNewSectionID
17
28
39
410

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 1

With the OUTPUT clause, Goldie...

-- goal is to copy over data for specified test

-- need to keep track of which sections map to each other

-- here's the insert

-- but how can i keep track of the mapping?

INSERT INTO @Sections(TestID, Name, Property, Sequence)

OUTPUT inserted.ID,inserted.TestID -- <----<<< LOOKY HERE!!!!

SELECT TestID

,Name

,Property

,Sequence

FROM @Sections

WHERE TestID = 1

SELECT *

FROM @Sections

If you need to store the results from the OUTPUT clause somewhere, it would be done like this in this case...

--===== Have a tracking table somewhere

DECLARE @TrackMe TABLE (ID INT, TestID INT)

INSERT INTO @Sections(TestID, Name, Property, Sequence)

OUTPUT inserted.ID,inserted.TestID -- <----<<< LOOKY HERE!!!!

INTO @TrackMe (ID,TestID) -- and HERE!!!

SELECT TestID

,Name

,Property

,Sequence

FROM @Sections

WHERE TestID = 1

SELECT *

FROM @Sections

SELECT * FROM @TrackMe

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 2

That will give me the IDs of the newly inserted records.

But how do I know which one of the original records they are a copy of?

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 3

Darned sorry about that, Goldie. I answered too quickly.

You would think that Microsoft would allow you to return data from the "from_table_name" on an insert like they do on an update... they don't. I don't know how to do this. Even a trigger would give a similar problem....

Any chance of just adding an "Original ID" column to the Sections Table? That would resolve the problem in this case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 4

Jeff Moden (8/6/2009)
Darned sorry about that, Goldie. I answered too quickly.
You would think that Microsoft would allow you to return data from the "from_table_name" on an insert like they do on an update... they don't. I don't know how to do this. Even a trigger would give a similar problem....
Any chance of just adding an "Original ID" column to the Sections Table? That would resolve the problem in this case.

Yes it is an annoying, if understandable, restriction when using an OUTPUT clause with INSERT.

Happily, in 2008, we can do this using MERGE:

MERGE INTO @Sections

USING (SELECT ID, TestID, Name, Property, Sequence FROM @Sections WHERE TestID = 1) AS X

ON X.TestID = 0

WHEN NOT MATCHED THEN INSERT (TestID, Name, Property, Sequence) VALUES (X.TestID, X.Name, X.Property, X.Sequence)

OUTPUT $action, inserted.*, X.ID AS original_id;

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 5

Heh... I really do have to install the DevEd of 2k8. Thanks for the reminder.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 6

Paul White (8/8/2009)
Jeff Moden (8/6/2009)
Darned sorry about that, Goldie. I answered too quickly.
You would think that Microsoft would allow you to return data from the "from_table_name" on an insert like they do on an update... they don't. I don't know how to do this. Even a trigger would give a similar problem....
Any chance of just adding an "Original ID" column to the Sections Table? That would resolve the problem in this case.
Yes it is an annoying, if understandable, restriction when using an OUTPUT clause with INSERT.
Happily, in 2008, we can do this using MERGE:
MERGE INTO @Sections
USING (SELECT ID, TestID, Name, Property, Sequence FROM @Sections WHERE TestID = 1) AS X
ON X.TestID = 0
WHEN NOT MATCHED THEN INSERT (TestID, Name, Property, Sequence) VALUES (X.TestID, X.Name, X.Property, X.Sequence)
OUTPUT $action, inserted.*, X.ID AS original_id;
Wow that's smart. I never thought of using MERGE that way.
Luckily, I am working on a 2008 instance, I'm going to give it a try first thing Monday and let you know how it all works out.
Thanks a bunch!
Paul

Paul White SSC Guru Points: 150467 More actions · Answer 7

Thank you Goldie - it's always nice when people post back to let us know that we helped 🙂

I felt fairly safe using a 2008-only feature since this is the T-SQL (SS2K8) section :laugh:

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 8

Thanks so much to both of you for your help.

In the end I neded to create a GUID field on my Sections table for other reasons, so I can use that to match back up the sections.

Jeff, you should totally install 2k8. The new SSMS is much faster, even for accessing SQL Server 2005 databases.