Linked Primary Keys after INSERT within the same table

Question

Linked Primary Keys after INSERT within the same table

TBIG

SSC-Addicted

Points: 479
More actions
June 23, 2013 at 7:05 am

#98593

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?

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eric M Russell SSC Guru Points: 125519 More actions · Answer 2

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.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Lowell SSC Guru Points: 323504 More actions · Answer 3

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 One Orange Chip Points: 26944 More actions · Answer 4

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 SSC-Addicted Points: 479 More actions · Answer 5

TBIG

SSC-Addicted

Points: 479

June 26, 2013 at 10:13 am

#1627637

Thanks Lowell. OUTPUT will help.

TBIG SSC-Addicted Points: 479 More actions · Answer 6

Mark: My client's Sql Server is pre-2008, so MERGE is not available. It would be a good solution.

Thanks!

TBIG SSC-Addicted Points: 479 More actions · Answer 7

I found the solution here:

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