如何复制 SQL 表中的记录但换出新行的唯一 ID? [英] How do you copy a record in a SQL table but swap out the unique id of the new row?

问题描述

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

插入到MyTableselect * from MyTable where uniqueId = @Id;

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

我确信有一个简单的解决方案，只是我对 TSQL 了解不够，无法知道它是什么.

解决方案

试试这个:<预><代码>插入到 MyTable(field1, field2, id_backup)从 MyTable 中选择 field1、field2、uniqueId，其中 uniqueId = @Id；

任何未指定的字段都应获得其默认值(未定义时通常为 NULL).

This question comes 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:

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).

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

解决方案

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).

这篇关于如何复制 SQL 表中的记录但换出新行的唯一 ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！