Help required getting @@Identity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help required getting @@Identity

Author

Topic

alanmac
Starting Member

26 Posts

Posted - 2009-01-30 : 03:29:41

Hi guys,

I have a temp table in a stored procedure and having populated it I need to update a live table and update the ID column of the temp table with the identity values created for each insert.

The code looks like this so far:

INSERT DeliveryRequest
SELECT
DateAdded,
RequestorName,
RequestedDate,
RequiredDate,
PackedDate,
-- a few other columns here.
FROM #TempDeliveryRequest

The #TempDeliveryRequest table also has a column for DeliveryRequestID, which is the Identity column in the DeliveryRequest table.

Is there a way of inserting one row at a time from the #TempDeliveryRequest table and using the @@IDENTITY or Scope_Identity to populate the DeliveryRequestID for that row?

In short, as each identity value is generated in DeliveryRequest I want that value to be mirrored in the temp table. The reason for this is that I then update other tables using only the DeliveryRequestIDs generated by these inserts.

I hope that makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 03:37:41

make use of OUTPUT operator in sql 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 03:40:01

something like

INSERT DeliveryRequest
SELECT 
DateAdded,
RequestorName,
RequestedDate,
RequiredDate,
PackedDate
OUTPUT INSERTED.DeliveryRequestID,INSERTED.DateAdded.... INTO #Temp
FROM #TempDeliveryRequest

then make use of #Temp to get the id values later. also remember to create #Temp befor this

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-30 : 03:41:44

create proc usp_samp
( @id int output,
input parameters
)
set nocount on
begin

INSERT DeliveryRequest
SELECT
DateAdded,
RequestorName,
RequestedDate,
RequiredDate,
PackedDate,
-- a few other columns here.
FROM #TempDeliveryRequest

select @id = scope_identity()

or select @id = @@identity

insert into anothertable select @id,....
end
set nocount off

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 03:44:32

quote:
Originally posted by bklr

create proc usp_samp
( @id int output,
input parameters
)
set nocount on
begin

INSERT DeliveryRequest
SELECT
DateAdded,
RequestorName,
RequestedDate,
RequiredDate,
PackedDate,
-- a few other columns here.
FROM #TempDeliveryRequest

select @id = scope_identity()

or select @id = @@identity

insert into anothertable select @id,....
end
set nocount off

regardless of number of records in #TempDeliveryRequest table, the @id will always return only id value generated for last record. so this wont help OP to grab all the generated ids for last last batch

alanmac
Starting Member

26 Posts

Posted - 2009-01-30 : 04:40:21

Hi Visakh16,

I tried using the OUTPUT keyword but get an error saying "Incorrect syntax near 'INSERTED'."

What is INSERTED supposed to represent?

alanmac
Starting Member

26 Posts

Posted - 2009-01-30 : 04:58:18

I have tried this instead:
CREATE TABLE #TempDELIDs
(
DeliveryRequestID smallint
)

INSERT DeliveryRequest
SELECT
DateAdded,
RequestorName,
RequestedDate,
RequiredDate,
PackedDate
select scope_identity INTO #TempDELIDs
FROM #TempDeliveryRequest

I have the following at the top of the SP:

IF OBJECT_ID(N'tempdb..#TempDELIDs', N'U') IS NOT NULL
drop table #TempDELIDs

yet I get the error message:

There is already an object named '#TempDELIDs' in the database.

How can it be there if I am dropping it at the start of the SP?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources