?
Solved

Inserting values into a table

Posted on 2007-12-02
5
Medium Priority
?
205 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:barnesco
  • 3
5 Comments
 
LVL 11

Expert Comment

by:yuching
ID: 20393057
INSERT INTO PromoList
      VALUES (StoreID, PromoID)
SELECT getID(), PromoID FROM SourceList
0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20393070
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
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 2000 total points
ID: 20393083
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
 

Author Comment

by:barnesco
ID: 20393104
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20393110
just a quick note, use Scope_Identity() rather than @@identity. If you have triggers on your table, @@identity could be way way off.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question