Copying Parent and Child Records - SQL Server 2005

[ElishaThompson]

I recently had a problem requiring me to locate and copy all records pertaining to a particular client from numerous tables in a database on a given server to tables within an identical database on another server.

In essence;

Given a particular ClientID (primary key in a top-of-the-hierarchy table), I needed to be able to obtain all records in all tables (maintaining referential integrity) that pertain to the particular ClientID and copy them into the table with the same name on a destination server which contains an identical database.

I think I have found a solution to this, but being new to TSQL I am not sure it is the most efficient solution. It is currently taking about 11 seconds to execute and I am not sure how it would scale with database size and complexity.

More information on the problem and the source files are available here;

http://teenylookatcode.blogspot.com/2008/11/stored-procedure-for-copying-parent-and_03.html

I wondered if I may have overlooked anything?

Thanks,
Dawn