SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Primary Keys after INSERT within the same table


Linked Primary Keys after INSERT within the same table

Author
Message
TBIG
TBIG
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 96
I need to do an INSERT for a subset of records in a table to the same table. What is the best way to determine the primary key of the source record from an inserted record?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63033 Visits: 17959
TBIG (6/23/2013)
I need to do an INSERT for a subset of records in a table to the same table. What is the best way to determine the primary key of the source record from an inserted record?


Not really sure what you mean here. Wouldn't the primary key be in the row in you are selecting? Maybe if you could explain a little more clearly what you are trying to do and what your question is we can help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29176 Visits: 11514
TBIG (6/23/2013)
I need to do an INSERT for a subset of records in a table to the same table. What is the best way to determine the primary key of the source record from an inserted record?

You can add a nullable column to the table called something like Parent_ID. If the row originated from another row, then Parent_ID would be populated with the original row's ID. If Parent_ID is NULL, then it's an original row.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72418 Visits: 40942
do you mean how do you get teh newly created PK values for data you inserted? especially so you can then do a subsequent insert into a child table?

the answer there , i think, is that you want to look at the OUTPUT clause;

here's a simple example, but the details and columns would be specific yto your tables.

CREATE TABLE adds(
adid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
code VARCHAR(30) )

DECLARE @MyResults TABLE(
ID int,
newcode VARCHAR(30),
oldcode VARCHAR(30) )

Create Table adds(adid int identity(1,1) not null primary key, code varchar(30) )
INSERT INTO adds(code)
OUTPUT
INSERTED.adid,
INSERTED.code,
NULL
INTO @MyResults
SELECT 'aliceblue' UNION ALL SELECT 'antiquewhite' UNION ALL
SELECT 'aqua*' UNION ALL SELECT 'aqua*' UNION ALL
SELECT 'aquamarine' UNION ALL SELECT 'azure' UNION ALL
SELECT 'beige' UNION ALL SELECT 'bisque' UNION ALL
SELECT 'black*' UNION ALL SELECT 'black*' UNION ALL
SELECT 'blanchedalmond' UNION ALL SELECT 'blue*' UNION ALL
SELECT 'blue*' UNION ALL SELECT 'blueviolet' UNION ALL
SELECT 'brown' UNION ALL SELECT 'burlywood' UNION ALL
SELECT 'cadetblue'

declare @MyResults TABLE(ID int,newcode varchar(30),oldcode varchar(30) )
--now that i have the new keys in a table variable, i can isnert into another, related, child table, or update something based on these new ID's
UPDATE dbo.adds
SET code = UPPER(SUBSTRING(code, 1, LEN(code) - 3) )
OUTPUT
INSERTED.adid,
INSERTED.code,
DELETED.code
INTO @MyResults
WHERE LEFT(code,1) = 'a'

SELECT * FROM @MyResults
--results of update
ID newcode oldcode
1 ALICEB aliceblue
2 ANTIQUEWH antiquewhite
3 AQ aqua*
4 AQ aqua*
5 AQUAMAR aquamarine
6 AZ azure



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6483 Visits: 25553
Lowell (6/26/2013)
do you mean how do you get teh newly created PK values for data you inserted? especially so you can then do a subsequent insert into a child table?

the answer there , i think, is that you want to look at the OUTPUT clause;

here's a simple example, but the details and columns would be specific yto your tables.

CREATE TABLE adds(
adid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
code VARCHAR(30) )

DECLARE @MyResults TABLE(
ID int,
newcode VARCHAR(30),
oldcode VARCHAR(30) )

Create Table adds(adid int identity(1,1) not null primary key, code varchar(30) )
INSERT INTO adds(code)
OUTPUT
INSERTED.adid,
INSERTED.code,
NULL
INTO @MyResults
SELECT 'aliceblue' UNION ALL SELECT 'antiquewhite' UNION ALL
SELECT 'aqua*' UNION ALL SELECT 'aqua*' UNION ALL
SELECT 'aquamarine' UNION ALL SELECT 'azure' UNION ALL
SELECT 'beige' UNION ALL SELECT 'bisque' UNION ALL
SELECT 'black*' UNION ALL SELECT 'black*' UNION ALL
SELECT 'blanchedalmond' UNION ALL SELECT 'blue*' UNION ALL
SELECT 'blue*' UNION ALL SELECT 'blueviolet' UNION ALL
SELECT 'brown' UNION ALL SELECT 'burlywood' UNION ALL
SELECT 'cadetblue'

declare @MyResults TABLE(ID int,newcode varchar(30),oldcode varchar(30) )
--now that i have the new keys in a table variable, i can isnert into another, related, child table, or update something based on these new ID's
UPDATE dbo.adds
SET code = UPPER(SUBSTRING(code, 1, LEN(code) - 3) )
OUTPUT
INSERTED.adid,
INSERTED.code,
DELETED.code
INTO @MyResults
WHERE LEFT(code,1) = 'a'

SELECT * FROM @MyResults
--results of update
ID newcode oldcode
1 ALICEB aliceblue
2 ANTIQUEWH antiquewhite
3 AQ aqua*
4 AQ aqua*
5 AQUAMAR aquamarine
6 AZ azure






You can use MERGE to capture previous and new identity values, not possible with INSERT


MERGE dbo.adds
USING (SELECT adid, 'X' + code AS code FROM dbo.adds) AS source
ON (1 = 0)
WHEN NOT MATCHED THEN INSERT (code)
VALUES(code)
OUTPUT inserted.adid AS New_adid, source.adid AS Original_adid, source.code;



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




TBIG
TBIG
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 96
Thanks Lowell. OUTPUT will help.
TBIG
TBIG
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 96
Mark: My client's Sql Server is pre-2008, so MERGE is not available. It would be a good solution.
Thanks!
TBIG
TBIG
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 96
I found the solution here:

http://www.sqlteam.com/article/using-the-output-clause-to-capture-identity-values-on-multi-row-inserts
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search