Copy Multiple Records and Their Related Records With SQL

A nice little trick for copying multiple records and their related records with SQL.

Suppose you have a Users and Phones tables, you would like to copy users 1-3, you could go with a a cursor and copy one by one and all their phones. 

But

Its a waste of perfectly good CPU cycles.

Here's an example:

declare @fromUserId int = 1
declare @toUserId int = 3

declare @UsersOldNew table (OriginalUserId int, NewUserId int)

merge into Users u
using 
(
    select UserId, Username
    from Users
    where UserId between @fromUserId and @toUserId
) as originalUsers
    on 1=0
when not matched then
    insert (Username)
    values (originalUsers.Username + '_from_' + convert(nvarchar(10),originalUsers.UserID))
output originalUsers.UserId, inserted.UserId into @UsersOldNew;


insert into UserPhones
select NewUserId, PhoneNumber
from UserPhones
    join @UsersOldNew
        on [@UsersOldNew].OriginalUserId = UserPhones.UserID

Cool!

So how did we do it?
First, we declared a table variable to hold the old vs new identity, you can do it with any type of identity you want, int, guid, it doesn't mind.

Then we executed a merge with an always false match, this causes the merge to create a new record for every existing record (returned from originalUsers).

The 3rd part of the merge is an output clause, which writes a record of old and new identities to the temp table.

And the last part just joins on this table and insert a new phone record the old userid had but with the new userid inserted.

Simple, fast and very cool! good job Microsoft!

Read More

Tags: merge, output, sql

Dror Gluska August 03, 2012  1 comment