Solved

Inserting into one table and using IDENTITY of another

Posted on 2011-09-22
6
163 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
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 60

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 60

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

705 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