Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...