fetching column values after an insert statement in a procedure

Question

fetching column values after an insert statement in a procedure

Vijoy Kolady

SSChasing Mays

Points: 607
More actions
September 9, 2008 at 11:02 pm

#193331

Hi,
I have a requirement wherein i need to fire multiple sql insert statements in a defined manner.
What i need is once i insert a record into a table A, i need to fetch some of its column values with the help of @@ and use those values in the next insert sql.
Can anyone please help on that ?

Viewing 6 posts - 1 through 5 (of 5 total)

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

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

As you are doing an Insert then you would know the value of most fields. Is it just the Identity value created that you need? In which case you can load it from @@Identity.

If you have a more complicated requirement then you could consider a Trigger (in a Trigger you would have access to the temporary "Inserted" table which contains the records just inserted).

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Or if you need this in just one procedure, there is an OUTPUT clause that will allow you to return the inserted or updated records into a table variable.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Another vote for using the OUTPUT clause. That's incredibly useful for doing exactly what you're asking for.

Also, I wouldn't recommend using @@IDENTITY. @@IDENTITY is not limited by the scope of the operation and returns the latest Identity value. That may or may not be the one you want. SCOPE_IDENTITY() returns within the context of the operation. That's the one you want to use in most circumstances. However, remember that this only works for a single row of data. If you just inserted 50 rows, SCOPE_IDENTITY will only return one. OUTPUT can get all 50 including the identity values for all of them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Vijoy Kolady SSChasing Mays Points: 607 More actions · Answer 4

wow..this is great.

Thanks John, Michael and Grant for your valuable inputs.

Grant, this is exactly what i need. I have an insert statement which would get fired based on a select that might get me say 50- 100 rows at a time. So for me OUTPUT would definetely be the ideal way to go about.

Just one more thing,

Suppose my query says something like this :

" Insert into table A ( column names) Select column names

from table B"

Now, based on the above insert i need to fetch all the values of the Identity column for the rows inserted and push it into the statement below :

" Insert into table A ( identity column from above, othercolumn

names)

Select column names from table B"

Can you guide me as to how should i go about using an OUTPUT clause for this ?

Thanks,

Vijoy

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

This is psuedo-code, but something along these lines:

CREATE TABLE #MyIDs

(ID int)

INSERT INTO TableA

(...)

OUTPUT INSERTED.ID

INTO #MyIDs

SELECT ...

FROM TableB

Then you can obviously do what you want with the temporary table. use it in a JOIN or CROSS APPLY with the next set of INSERTS of child data say... Whatever.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning