SQL 从同一个表中复制行并更新 ID 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3770012/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy rows from the same table and update the ID column
提问by kalls
I have the following table
我有下表
I have inserted Product B to it and it gives me an ID of 15
我已将产品 B 插入其中，它给我的 ID 为 15
Then I have the definition table which is as follows.
然后我有如下的定义表。
I want to select the ProductDefinition rows where ProdID = 14 and replicate the same and insert it for ProdID = 15 like the following
我想选择 ProdID = 14 的 ProductDefinition 行并复制相同的行并将其插入 ProdID = 15，如下所示
How to achieve this using SQL code?
如何使用 SQL 代码实现这一点？
回答by Blrfl
INSERT INTO ProuctDefinition (ProdID, Definition, Desc)
SELECT
xxx, Definition, Desc
FROM
ProductDefinition
WHERE
ProdID = yyy
The xxxis your new ProdID and the yyyis your old one. This also assumes that DefIDis automagically populated on INSERT.
该xxx是你的新的ProdID和yyy是你的旧的。这也假设DefID是在 上自动填充的INSERT。
回答by Donald Powell
This will work with any column you choose. Not just primary key/ID.
这将适用于您选择的任何列。不仅仅是主键/ID。
INSERT INTO TableName (Column1, CustomID, Column3, Column4, Column5)
SELECT Column1, 'NewValue', Column3, Column4, Column5 FROM TableName
WHERE CustomID='OrigValue'
回答by onedaywhen
Can use MERGEon SQL Server 2008, has the advantage of using OUTPUTto return the DefIDvalues, assuming they are auto-generated e.g.
可以MERGE在 SQL Server 2008 上使用，具有OUTPUT用于返回DefID值的优点，假设它们是自动生成的，例如
MERGE INTO ProductDefinition
USING (
SELECT 16, P1.Definition, P1.Description
FROM ProductDefinition AS P1
WHERE P1.ProdID = 15
) AS source (ProdID, Definition, Description)
ON 0 = 1
WHEN NOT MATCHED THEN
INSERT (ProdID, Definition, Description)
VALUES (ProdID, Definition, Description)
OUTPUT inserted.DefID, inserted.ProdID,
inserted.Definition, inserted.Description;
回答by Ahmed Elzeiny
if you want to select all items (in condition the table not contains any primary keys)
如果要选择所有项目（条件是表不包含任何主键）
INSERT INTO [tabelName]
SELECT * FROM [tabelName]
WHERE (YourCondition)
in condition the table contains a primary keys, select only the columns that not primary key Like:
在表包含主键的情况下，只选择不是主键的列，例如：
INSERT INTO [tabelName]
SELECT col_1,col_2,col_n FROM [tabelName]
WHERE (YourCondition)
回答by Divya
If you want to replicate data in same table use this logic:
如果要复制同一个表中的数据，请使用以下逻辑：
first, insert statment where you want to insert...
首先，在要插入的地方插入语句...
insert into [table](column1,column2)
second, select statment from where you want to take data for insertion....
其次，从要插入数据的位置选择语句....
select (column1/'your value',column2/'your value') from [table]
now set filter which rows you want to duplicate
现在设置过滤器要复制的行
where (your condition)
as want to replicate same data for different customers i have used this query.
因为想为不同的客户复制相同的数据，我使用了这个查询。
回答by Prabin Poudel
If your id is not autoincrement or declared sequence and if u dont want to create a temporary table:
如果您的 id 不是自动增量或声明的序列，并且您不想创建临时表：
you can use:
您可以使用：
INSERT INTO Tabel1 SELECT ((ROW_NUMBER( ) OVER ( ORDER BY ID )) + (SELECT MAX(id) FROM Table1)) ,column2,coulmn3,'NewValue' FROM Tabel1 Where somecolumn='your value`
回答by Franta
Do you use Oracle? It does not have an automatic PK_generator, nothing to work for your INSERTsilently. However, it has SEQUENCEs, so let's use its NEXTVAL:
你使用甲骨文吗？它没有自动 PK_generator，没有什么可以INSERT默默地为您工作。但是，它有序列，所以让我们使用它NEXTVAL：
INSERT INTO Orders_tab (Orderno, Custno)
VALUES (Order_seq.NEXTVAL, 1032);
The INSERT operation is exactly the case for them, the purpose of a SEQUENCE, you just have to use it explicitly. More described: Managing Sequences # Using Sequences
INSERT 操作正是他们的情况， a 的目的SEQUENCE，您只需要显式使用它。更多描述：管理序列 # 使用序列
The node for Sequences is on the level of Tables, i.e. in the SQLdeveloper. Ours are ID_GENERATOR, in every DB.
Sequences 的节点位于 Tables 级别，即在 SQLdeveloper 中。我们的是ID_GENERATOR，在每个数据库中。
