June 23, 2013 at 7:05 am
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?
June 24, 2013 at 7:50 am
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/
June 26, 2013 at 9:33 am
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
June 26, 2013 at 9:40 am
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
June 26, 2013 at 9:53 am
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/61537June 26, 2013 at 10:13 am
Thanks Lowell. OUTPUT will help.
June 26, 2013 at 10:15 am
Mark: My client's Sql Server is pre-2008, so MERGE is not available. It would be a good solution.
Thanks!
June 26, 2013 at 1:33 pm
I found the solution here:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply