Solved

I have to create a link between the original row and the updated row when inserting a duplicate row

Posted on 2014-03-15
16
299 Views
Last Modified: 2014-03-17
Hi,

I have to duplicate a row in the database but I have to create a link between the original row and the updated row. The problem is, once the duplicate row is inserted, the id's have been updated and so I need a way of linking the existing record with the new.

Hope this makes sense.
0
Comment
Question by:jazz__man
[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
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 39932394
I reckon the existing ID does not change, though that could be the case from your description. You still have the issue then to know the new ID from the inserted row. Usually you select the max. current ID maybe filtered to get only rows with the "same" values.
0
 
LVL 78

Expert Comment

by:arnold
ID: 39932404
Presumably the new record is the one that needs to reflect the id of the row it updated such that only the original record will have no link reference
In this scenario, you probably have the record data you are updating.
Alternatively, are these duplicate rows the auditing trail that is in a completely different table?
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39933797
Suppose I need to duplicate the following

CustID    FName     SName
1234       Mike         Smith

after the insert it would be
CustID    FName     SName
1235       Mike         Smith

I need to create a temporary link between both existing and new records.
When I do the insert it would be good to have a temp table with

ExistCustID   New CustomerID
1234              1235

By doing this I would be able to link the old to the new, this is what I need to do, but it needs to be done for multiple row insert.
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39933816
I think you want to use the OUTPUT clause in the INSERT statment, see here:
http://technet.microsoft.com/en-us/library/ms177564.aspx
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39933932
... if CustID is an autonumber. Using OUTPUT you get the inserted row(s). After that, you will insert the new IDs in your reference table, and then search the original row by using the Fname and SName fields.
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39933942
Guy Hengel

Thanks for this, however I have looked into the output keyword and this only allows columns from the insert statement to be included in the output.

I actually need an id from two different tables to be matched.

Thanks
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39933943
Qlemo

Can you do a basic mockup of how this would work?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39934025
Are you doing that inside of an application, or with T-SQL?
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39934064
with T-SQL
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39934066
Been messing about with this idea but have not got it to work yet

MERGE T s
USING (
        SELECT TID, name, address
        FROM T [s]
      ) d on 0 = 1
WHEN NOT MATCHED
THEN INSERT (name, address)
    VALUES (name, address)
OUTPUT d.TID as oID, Inserted.TID as nID
INTO @t;
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39934100
> but have not got it to work yet
error message(s) are?
did you declare the @t table?

see this script which is working just fine
use  soap


go

create table tmp_ee ( id int identity , data varchar(10) )
go

insert into tmp_ee (data) values ( 'row 1')
insert into tmp_ee (data) values ( 'row 2')
go

declare @t table ( id1 int, id2 int )

merge tmp_ee t
using (select id, data from tmp_ee ) d
on ( 0 = 1 )
when not matched then insert (data) values (data)
output d.id, inserted.id into @t
;
select * from @t 
select * from tmp_ee


go
drop table tmp_ee

Open in new window

output
(1 row(s) affected)

(1 row(s) affected)

(2 row(s) affected)
id1         id2
----------- -----------
1           3
2           4

(2 row(s) affected)

id          data
----------- ----------
1           row 1
2           row 2
3           row 1
4           row 2

(4 row(s) affected)

Open in new window

as you can see, ID 1 is linked to ID 3 and ID 2 to ID 4

hope this clarifies
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39934133
I don't understand what the s is in MERGE T s

and then again in FROM T [s]
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39934154
that is an alias, for the table, during the query
see here my article about that concept:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39934187
I understand aliases but is 's' a different alias from '[s]'
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39934206
in this case it's the same. names can be enclosed by [ ]  , which is optional unless you want to use special characters like spaces etc ...

so, the following 4 are fully equivalent
FROM T s
FROM T [s]
FROM [T] s
FROM [T] [s]
0
 
LVL 2

Author Closing Comment

by:jazz__man
ID: 39934340
I haven't actually got it to work yet but I believe this is the way forward.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tempdb Contention - SQL SERVER 10 41
What type of testing am I doing? 4 76
I have an unknown large SQL database I want to get rid of 10 46
SQL Query 20 22
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 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