Cloning rows in T-SQL with their foreign key constraints

• Download source code - 5.61 KB

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. :)

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.