February 8, 2011 at 1:03 pm
I have a procedure where I do an insert by selecting data from a #temp table, i need one of the values from the insert, and the SCOPE_IDENTITY() of each insert to insert into another bridge table.
My current method only gets the info and does the insert for the last record of the first insert.
Is there a way to do this in SQL 2005? I have included an example of what I am trying to accomplish below.
TIA!
DECLARE @currentDateTime datetime
SET @currentDateTime = GetDate()
DECLARE @ClientID INT
DECLARE @col_a_value INT
BEGIN TRANS
INSERT Tbl1
(col_a, col_b, stdate)
SELECT
col_1,
col_2,
@currentDateTime As stdate
FROM #Temp TMP
LEFT OUTER JOIN Tbl3 T3 ON T3.ID = TMP.col_1
INNER JOIN Tbl4 T4 ON T4.col_A = TMP.col_3
WHERE TMP.col_2 NOT IN (V1, V2, V3)
SET @ClientID = SCOPE_IDENTITY()
SET @col_a_value = (SELECT col_a FROM Tbl1 WHERE ID = @ClientID)
INSERT Tbl5
(clientID, addID, isEnabled)
VALUES
(@ClientID, @col_a_value, 1)
COMMIT TRANS
February 8, 2011 at 1:05 pm
Bing/Google/whatever "t-sql output". In SQL 2005, they added an "output" clause to insert/update/delete statements (works with merge as well, in SQL 2008).
It will give you all the IDs and such in one go. Much better than repeatedly calling scope_identity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2011 at 1:17 pm
Just discovered it! I'm pretty sure that's exactly what I needed! From what I've read, I believe I can put an OUTPUT statement before the SELECT statement. Can I just use something like "OUTPUT inserted.ID, inserted.col_a INTO Tbl5"
February 8, 2011 at 1:22 pm
Yes, that's how it works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2011 at 1:22 pm
Edit: Double-post due to latency.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2011 at 1:49 pm
Thanks for your help G2!
I have tried both of the following statements:
DECLARE @currentDateTime datetime
SET @currentDateTime = GetDate()
DECLARE @ClientID INT
DECLARE @col_a_value INT
BEGIN TRANS
INSERT Tbl1
(col_a, col_b, stdate)
OUTPUT inserted.id, inserted.col_b, 1 INTO Tbl5
SELECT
col_1,
col_2,
@currentDateTime As stdate
FROM #Temp TMP
LEFT OUTER JOIN Tbl3 T3 ON T3.ID = TMP.col_1
INNER JOIN Tbl4 T4 ON T4.col_A = TMP.col_3
WHERE TMP.col_2 NOT IN (V1, V2, V3)
COMMIT TRANS
And this one:
DECLARE @currentDateTime datetime
SET @currentDateTime = GetDate()
DECLARE @ClientID INT
DECLARE @col_a_value INT
BEGIN TRANS
INSERT Tbl1
(col_a, col_b, stdate)
OUTPUT @@identity, inserted.col_b, 1 INTO Tbl5
SELECT
col_1,
col_2,
@currentDateTime As stdate
FROM #Temp TMP
LEFT OUTER JOIN Tbl3 T3 ON T3.ID = TMP.col_1
INNER JOIN Tbl4 T4 ON T4.col_A = TMP.col_3
WHERE TMP.col_2 NOT IN (V1, V2, V3)
COMMIT TRANS
And both give me the error: "[font="Courier New"]Insert Error: Column name or number of supplied values does not match table definition.[/font]"
Do I need to Alias the columns I am inserting using the OUTPUT INTO Tbl5?
Any advice you or others could give me is greatly appreciated!
TIA,
KK
February 9, 2011 at 7:21 am
You can bypass the table variable step if you specify the columns in Tbl5 in the Output clause.
OUTPUT inserted.MyCol INTO dbo.Tbl5(Tbl5Col)
You can list the columns you want to insert into in the parentheses after the table name. Just like any normal insert statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2011 at 8:20 am
Thanks! That was what was eluding me.
Appreciate the guidance!
KK
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply