| Author |
 Topic  |
|
|
alanmac
Starting Member
United Kingdom
24 Posts |
 Posted - 01/30/2009 : 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
India
47023 Posts |
Posted - 01/30/2009 : 03:37:41
|
make use of OUTPUT operator in sql 2005
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/30/2009 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 01/30/2009 : 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
India
47023 Posts |
Posted - 01/30/2009 : 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
United Kingdom
24 Posts |
Posted - 01/30/2009 : 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
United Kingdom
24 Posts |
Posted - 01/30/2009 : 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?
|
|
|
| |
Topic |
|
Help required getting @@Identity
