SQL克隆记录与唯一索引 [英] SQL clone record with a unique index

问题描述

有一个干净的方式克隆SQL中有索引（自动增量）的记录。我想克隆除索引之外的所有字段。我现在必须枚举每个字段，并在插入选择中使用它，我不想显式列出所有字段，因为它们可能会随时间的变化。

Is there a clean way of cloning a record in SQL that has an index(auto increment). I want to clone all the fields except the index. I currently have to enumerate every field, and use that in an insert select, and I would rather not explicitly list all of the fields, as they may change over time.

推荐答案

除非你想进入动态SQL。

Not unless you want to get into dynamic SQL. Since you wrote "clean", I'll assume not.

编辑：由于他要求一个动态SQL示例，我将采取刺伤它。我现在没有连接到任何数据库，所以这是我的头顶，几乎肯定需要修订。但希望它捕捉到了事物的精神：

Since he asked for a dynamic SQL example, I'll take a stab at it. I'm not connected to any databases at the moment, so this is off the top of my head and will almost certainly need revision. But hopefully it captures the spirit of things:

-- Get list of columns in table
SELECT INTO #t
EXEC sp_columns @table_name = N'TargetTable'

-- Create a comma-delimited string excluding the identity column
DECLARE @cols varchar(MAX)
SELECT @cols = COALESCE(@cols+',' ,'') + COLUMN_NAME FROM #t WHERE COLUMN_NAME <> 'id'

-- Construct dynamic SQL statement
DECLARE @sql varchar(MAX)
SET @sql = 'INSERT INTO TargetTable (' + @cols + ') ' +
    'SELECT ' + @cols + ' FROM TargetTable WHERE SomeCondition'

PRINT @sql -- for debugging
EXEC(@sql)

这篇关于SQL克隆记录与唯一索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！