Quickest way to clone row in SQL

问题:

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?

---

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.

回答1:

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.

回答2:

You can run something like the stored procedure below to avoid typing out all of the column names. The example below assumes an int, but you can swap the key type out for any data type.

create procedure [CloneRow]
    @tableName varchar(max),
    @keyName varchar(max),
    @oldKeyId int,
    @newTableId int output
as
    declare @sqlCommand nvarchar(max),
            @columnList varchar(max);

    select  @columnList = coalesce(@columnList + ',','') + sys.columns.name
    from    sys.columns
    where   object_name(sys.columns.object_id) = @tableName
        and sys.columns.name not in ( @keyName )
        and is_computed = 0;

    set @sqlCommand = 'insert into ' + @tableName + ' ( ' + @columnList + ') (' +
        'select ' + @columnList + ' from ' + @tableName + ' where ' + @keyName + ' = @oldKeyId )'
    exec sp_executesql @sqlCommand, N'@oldKeyId int', @oldKeyId = @oldKeyId
    select @newTableId = @@identity -- note scope_identity() won't work here!
GO

You call it like this:

declare @newOrderId int
exec [CloneRow] 'orderTable', 'orderId', 625911, @newOrderId output

回答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;

回答4:

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;

回答5:

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'