Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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

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
jazz__man
Asked:
jazz__man
  • 8
  • 4
  • 3
  • +1
1 Solution
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
arnoldCommented:
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
 
jazz__manAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
... 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
 
jazz__manAuthor Commented:
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
 
jazz__manAuthor Commented:
Qlemo

Can you do a basic mockup of how this would work?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Are you doing that inside of an application, or with T-SQL?
0
 
jazz__manAuthor Commented:
with T-SQL
0
 
jazz__manAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> 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
 
jazz__manAuthor Commented:
I don't understand what the s is in MERGE T s

and then again in FROM T [s]
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jazz__manAuthor Commented:
I understand aliases but is 's' a different alias from '[s]'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jazz__manAuthor Commented:
I haven't actually got it to work yet but I believe this is the way forward.
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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now