INSERT INTO 语句复制行并自动递增非标识键 ID 列 [英] INSERT INTO statement that copies rows and auto-increments non-identity key ID column

问题描述

给定一个包含三列的表格

Given a table that has three columns

1. ID(主键，非自动递增)
2. 组 ID
3. SomeValue

我正在尝试编写一个 SQL INSERT INTO 语句，该语句会将具有一个 GroupID 的每一行复制到一个新的 GroupID 中.

I am trying to write a single SQL INSERT INTO statement that will make a copy of every row that has one GroupID into a new GroupID.

示例起始表:

ID | GroupID | SomeValue
------------------------
1  |    1    |    a
2  |    1    |    b

运行简单的 INSERT INTO 语句后的目标:

Goal after I run a simple INSERT INTO statement:

ID | GroupID | SomeValue
------------------------
1  |    1    |    a
2  |    1    |    b
3  |    2    |    a
4  |    2    |    b

我以为我可以这样做:

INSERT INTO MyTable
(       [ID]
       ,[GroupID]
       ,[SomeValue]
)
(
SELECT (SELECT MAX(ID) + 1 FROM MyTable)
       ,@NewGroupID
       ,[SomeValue]
 FROM MyTable
 WHERE ID = @OriginalGroupID
)

这会导致主键违规，因为它最终会多次重复使用相同的 Max(ID)+1 值.

This causes a PrimaryKey violation since it will end up reusing the same Max(ID)+1 value multiple times as it seems.

我是否只能求助于具有递增计数器值的 T-SQL WHILE 语句中的一堆 INSERT 语句?

Is my only recourse to a bunch of INSERT statements in a T-SQL WHILE statement that has an incrementing Counter value?

我也无法将 ID 转换为自动递增的 Identity 列，因为这会破坏我没有源代码的代码.

I also don't have the option of turning the ID into an auto-incrementing Identity column since that would breaking code I don't have source for.

推荐答案

代替 +1，添加行号.我还修复了 WHERE 子句中的错误(应该是 GroupID =，而不是 ID =):

Instead of + 1, add the row number. I also fixed the error in your WHERE clause (should be GroupID =, not ID =):

INSERT INTO MyTable
(       [ID]
       ,[GroupID]
       ,[SomeValue]
)
(
    SELECT
       (SELECT MAX(ID) FROM MyTable) + ROW_NUMBER() OVER (ORDER BY GroupId),
       @NewGroupID,
       [SomeValue]
    FROM MyTable
    WHERE GroupID = @OriginalGroupID
)

这篇关于INSERT INTO 语句复制行并自动递增非标识键 ID 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！