Solved

Inserting values into a table

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 37
How to share SSIS Package? 6 37
TSQL - How to declare table name 26 31
T-SQL: Do I need CLUSTERED here? 13 45
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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