如何复制 SQL 表中的记录但换出新行的唯一 ID？

How do you copy a record in a SQL table but swap out the unique id of the new row?

提问by Kilhoffer

This questioncomes close to what I need, but my scenario is slightly different. The source table and destination table are the same and the primary key is a uniqueidentifier (guid). When I try this:

这个问题接近我需要的，但我的情况略有不同。源表和目标表相同，主键是唯一标识符（guid）。当我尝试这个时：

insert into MyTable
    select * from MyTable where uniqueId = @Id;

I obviously get a primary key constraint violation, since I'm attempting to copy over the primary key. Actually, I don't want to copy over the primary key at all. Rather, I want to create a new one. Additionally, I would like to selectively copy over certain fields, and leave the others null. To make matters more complex, I need to take the primary key of the original record, and insert it into another field in the copy (PreviousId field).

我显然遇到了主键约束违规，因为我试图复制主键。实际上，我根本不想复制主键。相反，我想创建一个新的。此外，我想有选择地复制某些字段，并将其他字段保留为空。更复杂的是，我需要获取原始记录的主键，并将其插入副本中的另一个字段（PreviousId 字段）。

I'm sure there is an easy solution to this, I just don't know enough TSQL to know what it is.

我确信有一个简单的解决方案，我只是不知道足够的 TSQL 来知道它是什么。

回答by AaronSieb

Try this:

尝试这个：

insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

Any fields not specified should receive their default value (which is usually NULL when not defined).

任何未指定的字段都应接收其默认值（未定义时通常为 NULL）。

回答by Jonas

Ok, I know that it's an old issue but I post my answer anyway.

好的，我知道这是一个老问题，但我还是发布了我的答案。

I like this solution. I only have to specify the identity column(s).

我喜欢这个解决方案。我只需要指定标识列。

SELECT * INTO TempTable FROM MyTable_T WHERE id = 1;
ALTER TABLE TempTable DROP COLUMN id;
INSERT INTO MyTable_T SELECT * FROM TempTable;
DROP TABLE TempTable;

The "id"-column is the identity column and that's the only column I have to specify. It's better than the other way around anyway. :-)

“id”列是标识列，这是我必须指定的唯一列。无论如何，这比其他方式要好。:-)

I use SQL Server. You may want to use "CREATE TABLE" and "UPDATE TABLE" at row 1 and 2. Hmm, I saw that I did not really give the answer that he wanted. He wanted to copy the id to another column also. But this solution is nice for making a copy with a new auto-id.

我使用 SQL Server。您可能想在第 1 行和第 2 行使用“ CREATE TABLE”和“ UPDATE TABLE”。嗯，我看到我没有真正给出他想要的答案。他还想将 id 复制到另一列。但是这个解决方案非常适合使用新的自动 ID 制作副本。

I edit my solution with the idéas from Michael Dibbets.

我用 Michael Dibbets 的想法编辑了我的解决方案。

use MyDatabase; 
SELECT * INTO #TempTable FROM [MyTable] WHERE [IndexField] = :id;
ALTER TABLE #TempTable DROP COLUMN [IndexField]; 
INSERT INTO [MyTable] SELECT * FROM #TempTable; 
DROP TABLE #TempTable;

You can drop more than one column by separating them with a ",". The :id should be replaced with the id of the row you want to copy. MyDatabase, MyTable and IndexField should be replaced with your names (of course).

您可以通过用“,”分隔来删除多于一列。:id 应替换为您要复制的行的 id。MyDatabase、MyTable 和 IndexField 应替换为您的姓名（当然）。

回答by Matt Hinze

I'm guessing you're trying to avoid writing out all the column names. If you're using SQL Management Studio you can easily right click on the table and Script As Insert.. then you can mess around with that output to create your query.

我猜你是想避免写出所有的列名。如果您使用的是 SQL Management Studio，您可以轻松地右键单击表和脚本作为插入...然后您可以处理该输出以创建您的查询。

回答by Scott Bevington

Specify all fields but your ID field.

指定除您的 ID 字段之外的所有字段。

INSERT INTO MyTable (FIELD2, FIELD3, ..., FIELD529, PreviousId)
SELECT FIELD2, NULL, ..., FIELD529, FIELD1
FROM MyTable
WHERE FIELD1 = @Id;

回答by TonyT

I have the same issue where I want a single script to work with a table that has columns added periodically by other developers. Not only that, but I am supporting many different versions of our database as customers may not all be up-to-date with the current version.

我有同样的问题，我想要一个脚本来处理一个表，该表具有由其他开发人员定期添加的列。不仅如此，我还支持我们数据库的许多不同版本，因为客户可能不会全部使用当前版本。

I took the solution by Jonas and modified it slightly. This allows me to make a copy of the row and then change the primary key before adding it back into the original source table. This is also really handy for working with tables that do not allow NULL values in columns and you don't want to have to specify each column name in the INSERT.

我采用了 Jonas 的解决方案并稍微修改了它。这允许我制作该行的副本，然后在将其添加回原始源表之前更改主键。这对于处理列中不允许 NULL 值的表也非常方便，并且您不想在 INSERT 中指定每个列名。

This code copies the row for 'ABC' to 'XYZ'

此代码将“ABC”的行复制到“XYZ”

SELECT * INTO #TempRow FROM SourceTable WHERE KeyColumn = 'ABC';
UPDATE #TempRow SET KeyColumn = 'XYZ';
INSERT INTO SourceTable SELECT * FROM #TempRow;
DELETE #TempRow;

Once you have finished the drop the temp table.

完成删除临时表后。

DROP TABLE #TempRow;

回答by Jeyara

I know my answer is late to the party. But the way i solved is bit different than all the answers.

我知道我的回答迟到了。但是我解决的方式与所有答案都有些不同。

I had a situation, i need to clone a row in a table except few columns. Those few will have new values. This process should support automatically for future changes to the table. This implies, clone the record without specifying any column names.

我有一个情况，我需要在表中克隆除几列之外的一行。那少数人将拥有新的价值观。此过程应自动支持将来对表的更改。这意味着，在不指定任何列名的情况下克隆记录。

My approach is to,

我的做法是，

1. Query Sys.Columns to get the full list of columns for the table and include the names of columns to skip in where clause.
2. Convert that in to CSV as column names.
3. Build Select ... Insert into script based on this.

1. 查询 Sys.Columns 以获取表的完整列列表，并包括要在 where 子句中跳过的列的名称。
2. 将其转换为 CSV 作为列名。
3. 构建选择...基于此插入到脚本中。

declare @columnsToCopyValues varchar(max), @query varchar(max)
SET @columnsToCopyValues = ''


--Get all the columns execpt Identity columns and Other columns to be excluded. Say IndentityColumn, Column1, Column2
Select @columnsToCopyValues = @columnsToCopyValues  + [name] + ', ' from sys.columns c where c.object_id = OBJECT_ID('YourTableName') and name not in ('IndentityColumn','Column1','Column2')
Select @columnsToCopyValues = SUBSTRING(@columnsToCopyValues, 0, LEN(@columnsToCopyValues))
print @columnsToCopyValues

Select @query = CONCAT('insert into YourTableName (',@columnsToCopyValues,', Column1, Column2) select ', @columnsToCopyValues, ',''Value1'',''Value2'',', ' from YourTableName where IndentityColumn =''' , @searchVariable,'''')

print @query
exec (@query)

回答by Jeffrey L Whitledge

insert into MyTable (uniqueId, column1, column2, referencedUniqueId)
select NewGuid(), // don't know this syntax, sorry
  column1,
  column2,
  uniqueId,
from MyTable where uniqueId = @Id

回答by Eduardo Campa?ó

If "key" is your PK field and it's autonumeric.

如果“key”是您的 PK 字段并且它是自动数字的。

insert into MyTable (field1, field2, field3, parentkey)
select field1, field2, null, key from MyTable where uniqueId = @Id

it will generate a new record, copying field1 and field2 from the original record

它将生成一个新记录，从原始记录中复制 field1 和 field2

回答by Rit Man

My table has 100 fields, and I needed a query to just work. Now I can switch out any number of fields with some basic conditional logic and not worry about its ordinal position.

我的表有 100 个字段，我需要一个查询才能工作。现在我可以用一些基本的条件逻辑来切换任意数量的字段，而不必担心它的顺序位置。

1. Replace the below table name with your table name

   SQLcolums = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = 'TABLE-NAME')"
   
   Set GetColumns = Conn.Execute(SQLcolums)
   Do WHILE not GetColumns.eof
   
   colName = GetColumns("COLUMN_NAME")
   

2. Replace the original identity field name with your PK field name

   IF colName = "ORIGINAL-IDENTITY-FIELD-NAME" THEN ' ASSUMING THAT YOUR PRIMARY KEY IS THE FIRST FIELD DONT WORRY ABOUT COMMAS AND SPACES
       columnListSOURCE = colName 
       columnListTARGET = "[PreviousId field name]"
   ELSE
       columnListSOURCE = columnListSOURCE & colName
       columnListTARGET = columnListTARGET & colName
   END IF
   
   GetColumns.movenext
   
   loop
   
   GetColumns.close    
   

3. Replace the table names again (both target table name and source table name); edit your whereconditions

   SQL = "INSERT INTO TARGET-TABLE-NAME (" & columnListTARGET & ") SELECT " & columnListSOURCE & " FROM SOURCE-TABLE-NAME WHERE (FIELDNAME = FIELDVALUE)" 
   Conn.Execute(SQL)
   

1. 将下表名称替换为您的表名

   SQLcolums = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = 'TABLE-NAME')"
   
   Set GetColumns = Conn.Execute(SQLcolums)
   Do WHILE not GetColumns.eof
   
   colName = GetColumns("COLUMN_NAME")
   

2. 将原始身份字段名称替换为您的 PK 字段名称

   IF colName = "ORIGINAL-IDENTITY-FIELD-NAME" THEN ' ASSUMING THAT YOUR PRIMARY KEY IS THE FIRST FIELD DONT WORRY ABOUT COMMAS AND SPACES
       columnListSOURCE = colName 
       columnListTARGET = "[PreviousId field name]"
   ELSE
       columnListSOURCE = columnListSOURCE & colName
       columnListTARGET = columnListTARGET & colName
   END IF
   
   GetColumns.movenext
   
   loop
   
   GetColumns.close    
   

3. 再次替换表名（目标表名和源表名）；编辑您的where条件

   SQL = "INSERT INTO TARGET-TABLE-NAME (" & columnListTARGET & ") SELECT " & columnListSOURCE & " FROM SOURCE-TABLE-NAME WHERE (FIELDNAME = FIELDVALUE)" 
   Conn.Execute(SQL)
   

回答by Denis Kutlubaev

You can do like this:

你可以这样做：

INSERT INTO DENI/FRIEN01P 
SELECT 
   RCRDID+112,
   PROFESION,
   NAME,
   SURNAME,
   AGE, 
   RCRDTYP, 
   RCRDLCU, 
   RCRDLCT, 
   RCRDLCD 
FROM 
   FRIEN01P      

There instead of 112 you should put a number of the maximum id in table DENI/FRIEN01P.

您应该在表 DENI/FRIEN01P 中放置一些最大 id 值而不是 112。