• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

Inserting into one table and using IDENTITY of another

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
brettr
Asked:
brettr
  • 2
  • 2
  • 2
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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
 
brettrAuthor Commented:
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
 
lluddenCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
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
 
brettrAuthor Commented:
@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
 
lluddenCommented:
The OUTPUT function is nice - it works with any number of inserts and never has scope issues.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now