SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help required getting @@Identity
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alanmac
Starting Member

United Kingdom
26 Posts

Posted - 01/30/2009 :  03:29:41  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/30/2009 :  03:37:41  Show Profile  Reply with Quote
make use of OUTPUT operator in sql 2005
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/30/2009 :  03:40:01  Show Profile  Reply with Quote
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
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 01/30/2009 :  03:41:44  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/30/2009 :  03:44:32  Show Profile  Reply with Quote
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
Go to Top of Page

alanmac
Starting Member

United Kingdom
26 Posts

Posted - 01/30/2009 :  04:40:21  Show Profile  Reply with Quote
Hi Visakh16,

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

What is INSERTED supposed to represent?
Go to Top of Page

alanmac
Starting Member

United Kingdom
26 Posts

Posted - 01/30/2009 :  04:58:18  Show Profile  Reply with Quote
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?

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000