Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Primary Keys after INSERT within the same table Expand / Collapse
Author
Message
Posted Sunday, June 23, 2013 7:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 11, 2014 10:37 AM
Points: 16, Visits: 80
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?
Post #1466521
Posted Monday, June 24, 2013 7:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 13,093, Visits: 12,573
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 Moden's 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)
Post #1466747
Posted Wednesday, June 26, 2013 9:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 1,716, Visits: 4,872
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.
Post #1467744
Posted Wednesday, June 26, 2013 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 12,905, Visits: 32,180
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1467748
Posted Wednesday, June 26, 2013 9:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:29 AM
Points: 1,678, Visits: 19,554
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;



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1467755
Posted Wednesday, June 26, 2013 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 11, 2014 10:37 AM
Points: 16, Visits: 80
Thanks Lowell. OUTPUT will help.
Post #1467761
Posted Wednesday, June 26, 2013 10:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 11, 2014 10:37 AM
Points: 16, Visits: 80
Mark: My client's Sql Server is pre-2008, so MERGE is not available. It would be a good solution.
Thanks!
Post #1467763
Posted Wednesday, June 26, 2013 1:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 11, 2014 10:37 AM
Points: 16, Visits: 80
I found the solution here:

http://www.sqlteam.com/article/using-the-output-clause-to-capture-identity-values-on-multi-row-inserts
Post #1467843
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse