Solved

Inserting values into a table

Posted on 2007-12-02
5
197 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 500 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 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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