Solved

Inserting values into a table

Posted on 2007-12-02
5
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

717 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