Click here to Skip to main content
Click here to Skip to main content

Cloning rows in T-SQL with their foreign key constraints

, 16 Apr 2010
Rate this:
Please Sign up or sign in to vote.
When you copy a row with a primary key, bring other tables that reference it along for the ride!

Introduction

Copying a row in SQL is easy: select * into newTable from oldtable, and other variations.

But what if you want to copy tables that have foreign keys pointed to that row? For example, say you have a [users] table, and a [user_preferences] table with a foreign key pointing to [users].

Here, I'll show you how to do this without writing out code for all the tables you want to copy yourself.

Background

In order to use this code, you'll need a special aggregate function, strconcat. You can create your own easily in the CLR; check out this page for more details.

Using the code

To use this procedure, call it as follows:

declare @oldId bigint, @newId bigint 
set @oldId = 60000

--Clone the very first row manually, so we get a PK_ID
select *
    into #usersToClone
    from users
    where id= @oldId
alter table #usersToClone
    drop column id
insert into users
    select * from #usersToClone
set @newId = scope_identity()

--Include this table to exclude tables from being cloned
create table #table_ExcludeFromCloning(
    tableName nvarchar(max)
)

--Actual clone routine
exec table_CloneChildrenOfRow 'users', 'dbo', @oldId, @newId

How it works - recursion and other tricks in SQL

In order to automatically find rows of foreign key tables, we need to use a few tricks. Here are four of the biggest ones:

  1. First off, we need to get the foreign keys that point to a table. Fortunately, SQL Server has a Stored Procedure, sp_fkeys, that we can use for just that purpose.
  2. Second, once we get that list of foreign key tables, we need to execute SQL on each one. Here, we use sp_executesql to run SQL that we dynamically create.
  3. Third, our code is recursive SQL - we have one procedure that copies all the foreign key references pointing to a row, and another that copies all the rows for just one of those foreign key tables. They have to call each other, as a foreign key table may have its own primary key!
  4. Fourth, we have an interesting problem - how do we pass a table from one procedure to another? Table-valued parameters would help, but they're only in SQL Server 2008. In this case, we have to use some tricks with temp tables and table variables. Keep in mind that table variables only have scope within the current procedure, and temp tables have scope in the entire call stack - we have to use both!

Points of interest

This code works for simple tables with multiple foreign key tables referencing them, but there are certain weird scenarios (circular references, for example) that will break it.

If you have the luxury of working with an ORM, I suggest exploring that first before going this route. Smile | :)

Contact me with requests and updates!

Something not working? I've only tested this code on our local environment. Gmail me at chris.magradar.

History

  • Latest update - 16 Apr 2010 - new version adds support for different table owners.
  • First version - 30 Mar 2010.

License

This article, along with any associated source code and files, is licensed under The Eclipse Public License 1.0

About the Author

Christopher Liu

United States United States
Startup web developer, playing around with .NET, T-SQL, and other technologies.

Comments and Discussions

 
GeneralProblem in the INSERT after dropping the PK_ID column. INSERT still takes PK_ID value from SELECT PinmemberMarek Zgadzaj24-Sep-10 2:11 
Hi Christopher,
 
I cannot do the initlial step:
 
insert into users
    select * from #usersToClone
 
because SQL 2008 gives an error:
 
An explicit value for the identity column in table 'users' can only be specified when a column list is used and IDENTITY_INSERT is ON.
 
It means, that in spite of removing the PK_ID, during the INSERT from #usersToClone, one of the columns in INSERT is treated as a key kolumn.
 
Have you any idea what can I do ?
 
Thanks,
Marek
GeneralRe: Problem in the INSERT after dropping the PK_ID column. INSERT still takes PK_ID value from SELECT PinmemberChristopher Liu21-Nov-10 16:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 16 Apr 2010
Article Copyright 2010 by Christopher Liu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid