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.
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
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!