June 13, 2012 at 10:54 am
I'm inserting a subset of a table into the same table and in order to update some related tables need to capture both the newly created identity PK, and the matching old PK..
If SQL would support it, something like:
Create table Test (pk identity, description varchar(10))
Declare @PKVALUES TABLE (NewPK int, OLdPk int)
INSERT INTO Test (description)
OUTPUT INSERTED.PK, Test.PK into @PKVALUES
Select description
From Test
Where ...
But, of course, SQL doesn't support Output of values from the FROM table..
Is there some way I can accomplish this, (without having to resort to a one record at a time
approach) ?
Thanks,
Ilmar
June 13, 2012 at 11:32 am
iwaldner (6/13/2012)
I'm inserting a subset of a table into the same table and in order to update some related tables need to capture both the newly created identity PK, and the matching old PK..
The new PK IDs are from the OUTPUT clause and the matching old PK IDs are from the where clause. I don't understand which ID's you can't capture.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 13, 2012 at 11:43 am
It's the old PK from the where clause that I can't capture. Because SQL doesn't support the type of OUTPUT clause in my example.. It can only output from the INSERT. So I'm looking for a way to work around that limitation..
June 13, 2012 at 11:46 am
Use intermediate table to hold both the values... Something like this... You have to lock the table #Test in this approach
Create table #Test (pKeyCol int identity(1,1), Description varchar(10))
insert into #Test (Description) values('abc')
insert into #Test (Description) values('adfdfbc')
insert into #Test (Description) values('ddabc')
insert into #Test (Description) values('ddgbc')
insert into #Test (Description) values('eabc')
insert into #Test (Description) values('fabc')
insert into #Test (Description) values('gabc')
insert into #Test (Description) values('hhabc')
insert into #Test (Description) values('hhhabc')
insert into #Test (Description) values('gggabc')
select * from #Test where LEFT(description,1)='g'
Create table #Tmp (ID int identity(1,1), pKeyColOld int, Description varchar(10))
Insert into #Tmp(pKeyColOld,Description)
select pKeyCol,Description from #Test where LEFT(description,1)='g'
Declare @maxPK int
select @maxPK = ISNULL(max(pKeyCol),0) from #Test
set identity_insert #Test on
Insert into #Test(pKeyCol,Description)
select ID+@maxPK,Description from #Tmp
set identity_insert #Test off
select ID+@maxPK pkNew,pKeyColOld into #PKVALUES from #Tmp
select * from #PKVALUES
June 13, 2012 at 1:58 pm
That would work.. Though I really would prefer a solution that didn't involve locking the whole table...
I wonder why MSFT restricted the Output clause.. is there some technical reason why outputting from the "from" table wouldn't work, or is it just not part of the standard?
Thanks
Ilmar
June 13, 2012 at 1:58 pm
If you use MERGE , you can do this.
http://sqlblog.com/blogs/rob_farley/archive/2012/06/12/merge-gives-better-output-options.aspx
June 13, 2012 at 3:31 pm
iwaldner (6/13/2012)
I wonder why MSFT restricted the Output clause.. is there some technical reason why outputting from the "from" table wouldn't work, or is it just not part of the standard?
Query plans only carry values needed to perform the INSERT as far as the Insert operator, so it's a simple as the values not being there at the point where the OUTPUT clause is processed. As far as I know, there's no particular reason that the execution plan couldn't be modified to accommodate columns not needed for the INSERT but needed by the OUTPUT clause, but it doesn't do that today.
The MERGE workaround is not something I like very much, but it does work at the moment:
CREATE TABLE #Example
(
pk integer IDENTITY(1,1) PRIMARY KEY,
val character(1) NOT NULL
)
INSERT #Example
(val)
VALUES
('a'),
('b'),
('c');
MERGE #Example
USING #Example AS e ON 1 = 0
WHEN NOT MATCHED THEN INSERT (val) VALUES (e.val)
OUTPUT INSERTED.pk, e.pk;
SELECT *
FROM #Example AS e;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 13, 2012 at 4:48 pm
Why not just add a column in the table for the "old" id column?
It's only 4 more bytes, and you'd then be able to reference that column in the OUTPUT clause.
Something like this:
ALTER TABLE Test ADD old_pk int NULL
...
INSERT INTO Test (description, old_pk)
OUTPUT INSERTED.PK, INSERTED.old_pk into @PKVALUES
Select description, pk
From Test
Where ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 13, 2012 at 5:53 pm
Thanks for the explanation of the restriction on OUTPUT
The Merge I'll have to look at.. haven't used it before..
Thanks
June 13, 2012 at 5:55 pm
Adding a column to a production database is a "big deal" here.. and only as a last resort since it would be needed only for this one specialized task..
June 13, 2012 at 5:58 pm
iwaldner (6/13/2012)
The Merge I'll have to look at.. haven't used it before..Thanks
The syntax is a bit weird to start with, but you get used to it eventually 🙂
Inserting, Updating, and Deleting Data by Using MERGE
Optimizing MERGE Statement Performance
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply