sql server - 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?

---

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 1

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