How can I copy a SQL record which has related records in other tables to the same database?

Question

Hi.

I created a function in C# which allows me to copy a record and its related children to a new record and new related children in the same database. (This is for an application that allows the use of previous work as a template for new work.)

Anyway, it works great... Here's a description of how it accomplishes the copy:

It populates a two-column memory-based look-up table with the current primary key of each record. Next, as it individually creates each new copy record, it updates the look-up table with the Identity PK of the new record [retrieved from SCOPE_IDENTITY()]. Now, when it copies over any related children, it can look up the new parent PK to set the FK on the new record.

In testing, it only took a minute to copy a relational structure on a local instance of SQL Server 2005 Express Edition. Unfortunately it is proving to be horribly slow in production! My users are dealing with 60,000+ records per parent record over the LAN to our SQL Server! While my copy function still works, each of those records represents an individual SQL UPDATE command and it loads the SQL Server at about 17% CPU from its normal 2% idle. I just finished testing a 50,000 record copy and it took almost 20 minutes!

Is there a way to duplicate this functionality in SQL queries or stored procecures to make the SQL server do all of the copy work instead of blasting it over the LAN from each client?

(We're running Microsoft SQL Server 2005 Standard Edition.)

Thanks!

-Derek

Answer 1

Yes there is, if it's SQL 2005, you can potentially use a CLR stored procedure to install your C# code into the Db. To do it pure TSQL, it's just a matter of it being tedious and needing to use variables for the new keys. Is this just a case of one parent record and then a lot of child records that are only in one child table? You could potentially avoid issuing separate insert commands for each child row in this case by leveraging the fact that you can embed a select statement into an insert, especially if you're using identity columns for the PKs. That will usually be MUCH faster if you can do it. Something like this (syntax may be a bit off, but you'll need to adapt it to your real tables anyhow):

insert into tblchild select @newparentId, col1,col2 from tblChild where parentid=@oldparentid

Here's a starting point on CLR in SQL Server...

http://msdn.microsoft.com/en-us/library/ms131045.aspx (This article is SQL 2008, but much of it probably applies).

http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1

Answer 2

Would using (pseudo code)

INSERT INTO (PkField, FkField, OtherFields) SELECT NewPkValue, NewFkValue, OtherFields FROM TableName WHERE FkField = OldFkValue

help for the related child tables in that they will be batched rather than single updates per record?

Answer 3

why not put the copy calls into a stored proc and call the stored proc from c#.