Inserting values into a table

How do you insert an @@Identity value with values from another table. I tried:

DECLARE @NewStoreID int
SELECT @NewStoreID = @@Identity

INSERT INTO PromoList
      VALUES (StoreID, PromoID)
SELECT @NewStoreID, PromoID FROM SourceList

Obviously the @NewStoreID isn't in the SourceList, so how do you get @NewStoreID in with the PromoID?

Thanks.
barnescoAsked:
Who is Participating?
 
imitchieCommented:
So you have inserted into the Store table?, i.e.

INSERT INTO STORE(...) values (...)
DECLARE @NewStoreID int
SELECT @NewStoreID = @@Identity
INSERT INTO PromoList VALUES (StoreID, PromoID)
SELECT @NewStoreID, PromoID FROM SourceList

well, move the Declare out of the way just to make it clear

DECLARE @NewStoreID int

INSERT INTO STORE(...) values (...)
SELECT @NewStoreID = SCOPE_IDENTITY()
INSERT INTO PromoList VALUES (StoreID, PromoID)
SELECT @NewStoreID, PromoID

This works for a single record insert
0
 
yuchingCommented:
INSERT INTO PromoList
      VALUES (StoreID, PromoID)
SELECT getID(), PromoID FROM SourceList
0
 
imitchieCommented:
Are you trying to insert into SourceList, then copy the identity values across to PromoList? then try this

DECLARE @NewStoreID int
Insert into SourceList ( .. ) values (....)  -- is this where the StoreIDs are kept?

SELECT @NewStoreID = SCOPE_IDENTITY()
INSERT INTO PromoList VALUES (@NewStoreID, PromoID)
0
 
barnescoAuthor Commented:
You guys are fast!

I did figure it out, and I can't believe it's this simple, but it works:

DECLARE @NewStoreID int
SELECT @NewStoreID = @@Identity


INSERT INTO KScoop_PromoList (StoreID, PromoID)
      SELECT @NewStoreID, PromoID FROM SourceList
      

0
 
imitchieCommented:
just a quick note, use Scope_Identity() rather than @@identity. If you have triggers on your table, @@identity could be way way off.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.