Solved

Inserting into one table and using IDENTITY of another

Posted on 2011-09-22
6
158 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
ID: 36581852
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
ID: 36581944
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
ID: 36582964
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 200 total points
ID: 36583029
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
ID: 36583121
@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
ID: 36583170
The OUTPUT function is nice - it works with any number of inserts and never has scope issues.
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

Title # Comments Views Activity
how many extra RAM for SQL server is needed 22 43
get most recent and second most recent date in SQL Server 24 97
Restrict result set 1 39
SQL Query with Sum and Detail rows 2 54
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

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