Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Identity values with multi-row INSERT

Posted on 2004-09-20
3
Medium Priority
?
298 Views
Last Modified: 2008-03-06
Hello all,

I have a feeling the answer to this question is going to be a resounding "no," but I 'm wondering if there's a way to accomplish this without using a cursor: I have a table that has no valid natural key, so I use an identity column as a surrogate key. At times, certain rows in this table need to be duplicated. In other words, a group of rows will be selected based on given criteria, and all columns from those rows (except, obviously, the identity) will be inserted into new rows in the same table (with, obviously, new identities). I have to be able to link each newly created row back to the row it was created from in order to create an audit trail of which rows were duplicated into which other rows. I may not be explaining this well; let me know if it seems unclear.

So far, the only way I have found to accomplish this is to use a cursor to loop through the records to be duplicated, inserting each new record, getting its identity using SCOPE_IDENTITY(), then creating the audit trail record at that point. This works OK most of the time, but it seems inefficient to me. Does anyone have a better suggestion?

Thanks in advance!

Jeff
0
Comment
Question by:Jeff Tennessen
[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 Comments
 
LVL 7

Expert Comment

by:natloz
ID: 12105779
That sounds like the way I would do it...you need to get the Identity as you insert so that you can write it back to the other record...cursor sounds like the best way to do it...
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 1000 total points
ID: 12105793
Yes, you can. Here's a simple example. If your table has only two columns, ID (the identity) and ColA, then you add a third column, named "ID_Source". Then you do an insert like this:

INSERT TableA(ID_Source, ColA)
SELECT ID, ColA FROM TableA WHERE ...
0
 
LVL 8

Author Comment

by:Jeff Tennessen
ID: 12106197
Thanks, jdlambert1. Though it requires a schema modification, I think it might be worth it in this case. Much appreciated!

Jeff
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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