Quickest way to clone row in SQL

Question

I want to clone multiple tables' rows that have a single primary key (PrimKey). What's the quickest way to clone a row in SQL Server 2005?

Here's an example,

Clone PrimKey1 to get PrimKey2. So I try the following :

INSERT INTO PrimKeys 
SELECT 'PrimKey2' AS PrimKey,* 
  FROM PrimKeys 
 WHERE PrimKey='PrimKey1'

But of course the issue here is, the column PrimKey gets repeated in the inner SELECT statement. Is there any other way similar in simplicity to the above?

Answer 1

Not sure what do you mean by "multiple tables' rows that have a single primary key".

PRIMARY KEY, by definition, is UNIQUE.

To do your query you need to enumerate all columns:

INSERT
INTO    PrimKeys (PrimKey, col1, col2, …)
SELECT  'PrimKey2' AS PrimKey, col1, col2, …
FROM    PrimKeys
WHERE   PrimKey = 'PrimKey1'

Answer 2

If you are cloning from multiple tables, the fastest way will be to use a stored procedure, so that all the queries stay on the database, and you don't pay a price for communication between the client and the server.

Once you do that, then start unit-testing, to see how long it takes to do the operation. Then begin experimenting with changing it, based on suggestions you get, and see if you get any improvement.

Answer 3

This is not the most beautiful solution, but I think it will work for you. First of all, you select your data into a temporary table with a "new" primary key and next you drop the old primary key column from the temp table and use the temp table to insert your "cloned" row.

SELECT 
'PrimKey2' AS NewPrimKey,
*
INTO #TMP 
FROM PrimKeys 
WHERE PrimKey='PrimKey1';

ALTER TABLE #TMP DROP COLUMN PrimKey;

INSERT INTO PrimKeys
SELECT * FROM #TMP;

Answer 4

Thank you all for your responses. I went ahead and wrote a function that handles that in my application. I don't use Stored Procs or Temp tables so I couldn't use a couple of valid answers posted by some of you.

Answer 5

First, if you need to perform generic operations against a large number of tables, then dynamic SQL and the system tables are your friends.

Short of that, Hakan's solution will work for non-identity PKs. I would tighten it up to:

SELECT * INTO #TMP 
FROM PrimKeys 
WHERE PrimKey='PrimKey1';

UPDATE #TMP SET PrimeKey = 'PrimeKey2';

INSERT INTO PrimKeys
SELECT * FROM #TMP;

For identity PKs, change the UPDATE above to a DROP COLUMN:

SELECT * INTO #TMP 
FROM PrimKeys 
WHERE PrimKey=101;

ALTER TABLE #TMP DROP COLUMN PrimeKey;

INSERT INTO PrimKeys
SELECT * FROM #TMP;