Solved

Inserting into one table and using IDENTITY of another

Posted on 2011-09-22
6
156 Views
Last Modified: 2012-05-12
I'm looping through one table and inserting into another.  The IDENTITY value for the target table is coming from a 3rd table.  The code below is wrong but expresses what I'm trying to do.  I need to insert into table1 first and use its IDENTITY value for col1 in table2.  So, the table1 insert needs to move into the looping through table3.  How can this be done?
insert into table1 (col1, col2, col3)
values(1, 2, 1)

insert into table2 (col1, col2, col3)
select SCOPE_IDENTITY(), 1, 1)
from table3

Open in new window

0
Comment
Question by:brettr
  • 2
  • 2
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Do you mean like this:

INSERT INTO table1(col1, col2, col3)
VALUES(1, 2, 1);

DECLARE @ID BIGINT;
SELECT @ID = SCOPE_IDENTITY();

INSERT INTO table2(col1, col2, col3)
SELECT @ID, 1, 1
FROM table3;

Now, to be clear, you are not selecting any values at all from table 3. You appear to be using it for its number of rows? Is that what you mean to do?
0
 

Author Comment

by:brettr
Comment Utility
My example was simple.  I am selecting some values from table3 but some are hard coded.  I should have shown that better.

Your example does basically the same as mine, which won't work because it will continue inserting the same ID into table2.  That column is a PK.  

table1 should insert just before each insert into table2.  This way, table2 gets a new ID for its PK.
0
 
LVL 18

Expert Comment

by:lludden
Comment Utility
Use the OUTPUT clause:

INSERT INTO TestA (TestA.Val1, TestA.Val2)
OUTPUT Inserted.* INTO TestB
VALUES (2,4)

SELECT * FROM TestA
SELECT * FROM TestB
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 200 total points
Comment Utility
Is there a correlation to the rows in Table3 to entries in Table1.i.e., does it matter which IDENTITY goes to which row of Table3? If so, then you can probably do something like shown with OUTPUT. You do all the inserts and then using the table itself OR the target of OUTPUT to join to Table3 to do you final insert to Table2.
0
 

Author Comment

by:brettr
Comment Utility
@mwvisa1

Pretty close.  table1 and table2 match against empid.  Doesn't matter which ident goes into table2.  Just that the ident being inserted needs to match against the correct emptid.

I'm trying the OUTPUtT technique mentioned.  Thanks.
0
 
LVL 18

Accepted Solution

by:
lludden earned 300 total points
Comment Utility
The OUTPUT function is nice - it works with any number of inserts and never has scope issues.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now