如何复制多行,更改列值并插入同一个表中

时间:2022-02-10 20:36:53

Lets say I have a table T that has columns T_Id, A, and B. I want to find all rows that have a specific A value, increment the T_Id value and insert the new row into T with a new A value.

假设我有一个包含T_Id,A和B列的表T.我想查找具有特定A值的所有行,增加T_Id值并使用新的A值将新行插入T中。

For Example:

If I have the following table

如果我有下表

| T_Id | A   | B      |
|------|-----|--------|
| 1    | ABC | Value1 |
| 2    | ABC | Value2 |
| 3    | BCD | Value3 |

I would like the resulting table to look something like this:

我希望结果表看起来像这样:

| T_Id | A   | B      |
|------|-----|--------|
| 1    | ABC | Value1 |
| 2    | ABC | Value2 |
| 3    | BCD | Value3 |
| 4    | CDE | Value1 |
| 5    | CDE | Value2 |

The current script I have attempting to do this is as follows:

我试图这样做的当前脚本如下:

INSERT INTO [T]([T_Id],[A],[B])
SELECT Count(*)+1,'CDE',[B]
FROM [T]
WHERE [A] = 'ABC'

However, I receive this error upon executing Column 'T.B' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I believe this is not working as there are multiple records that will be returned from the SELECT statement.

但是,我在执行列'T.B'在选择列表中无效时收到此错误,因为它不包含在聚合函数或GROUP BY子句中。我相信这不起作用,因为有多个记录将从SELECT语句返回。

Any suggestions on how to remedy this situation would be appreciated!

任何有关如何纠正这种情况的建议将不胜感激!

2 个解决方案

#1


0  

INSERT INTO [T]([T_Id],[A],[B])
SELECT (SELECT MAX(T_Id) FROM T) + 1,'CDE',[B]
  FROM [T]
 WHERE [A] = 'ABC'
 ORDER BY T_Id ASC

This should work. You cant use the aggregation function count if you have columns that are not in the group by cause.

这应该工作。如果列中的列不在原因中,则无法使用聚合函数计数。

#2


0  

Any suggestions on how to remedy this situation would be appreciated!

任何有关如何纠正这种情况的建议将不胜感激!

I suggest you make T_id an IDENTITY column. Then you don't have to worry about it in the insert, and you can just do the INSERT..SELECT you have already written, without the T_id reference.

我建议你让T_id成为IDENTITY列。然后你不必在插入中担心它,你可以在没有T_id引用的情况下执行已经编写的INSERT..SELECT。

#1


0  

INSERT INTO [T]([T_Id],[A],[B])
SELECT (SELECT MAX(T_Id) FROM T) + 1,'CDE',[B]
  FROM [T]
 WHERE [A] = 'ABC'
 ORDER BY T_Id ASC

This should work. You cant use the aggregation function count if you have columns that are not in the group by cause.

这应该工作。如果列中的列不在原因中,则无法使用聚合函数计数。

#2


0  

Any suggestions on how to remedy this situation would be appreciated!

任何有关如何纠正这种情况的建议将不胜感激!

I suggest you make T_id an IDENTITY column. Then you don't have to worry about it in the insert, and you can just do the INSERT..SELECT you have already written, without the T_id reference.

我建议你让T_id成为IDENTITY列。然后你不必在插入中担心它,你可以在没有T_id引用的情况下执行已经编写的INSERT..SELECT。