SQL insert into ... select *，如何忽略标识？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/122639/
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
Insert into ... Select *, how to ignore identity?
提问by Haoest
I have a temp table with the exact structure of a concrete table T. It was created like this:
我有一个具有具体表结构的临时表T。它是这样创建的：
select top 0 * into #tmp from T
After processing and filling in content into #tmp, I want to copy the content back to Tlike this:
处理并填写内容后#tmp，我想将内容复制回T这样：
insert into T select * from #tmp
This is okay as long as Tdoesn't have identity column, but in my case it does. Is there any way I can ignore the auto-increment identity column from #tmpwhen I copy to T? My motivation is to avoid having to spell out every column name in the Insert Into list.
只要T没有标识列就可以，但在我的情况下是这样。有什么办法可以忽略从#tmp复制到时的自动递增标识列T？我的动机是避免在 Insert Into 列表中拼出每个列名。
EDIT: toggling identity_insert wouldn't work because the pkeys in #tmpmay collide with those in Tif rows were inserted into Toutside of my script, that's if #tmphas auto-incremented the pkey to sync with T's in the first place.
编辑：切换 identity_insert 不起作用，因为如果行被插入到我的脚本之外，pkeys in#tmp可能会与那些 in 冲突，也就是说，如果首先自动增加 pkey 以与 T 同步。TT#tmp
采纳答案by DK.
As identity will be generated during insert anyway, could you simply remove this column from #tmp before inserting the data back to T?
由于无论如何都会在插入过程中生成身份，您能否在将数据插入回 T 之前简单地从 #tmp 中删除此列？
alter table #tmp drop column id
UPD:Here's an example I've tested in SQL Server 2008:
UPD：这是我在 SQL Server 2008 中测试过的示例：
create table T(ID int identity(1,1) not null, Value nvarchar(50))
insert into T (Value) values (N'Hello T!')
select top 0 * into #tmp from T
alter table #tmp drop column ID
insert into #tmp (Value) values (N'Hello #tmp')
insert into T select * from #tmp
drop table #tmp
select * from T
drop table T
回答by Orion Adrian
SET IDENTITY_INSERT ON
设置 IDENTITY_INSERT ON
INSERT command
插入命令
SET IDENTITY_INSERT OFF
设置 IDENTITY_INSERT 关闭
回答by s????
select * into without_id from with_id
union all
select * from with_id where 1 = 0
Reason:
原因：
When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
- The SELECT statement contains a join, GROUP BY clause, or aggregate function.
- Multiple SELECT statements are joined by using UNION.
- The identity column is listed more than one time in the select list.
- The identity column is part of an expression.
- The identity column is from a remote data source.
If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. See "Creating an identity column using the IDENTITY function" in the Examples section below.
将现有标识列选入新表时，新列将继承 IDENTITY 属性，除非满足以下条件之一：
- SELECT 语句包含连接、GROUP BY 子句或聚合函数。
- 多个 SELECT 语句使用 UNION 连接。
- 标识列在选择列表中多次列出。
- 标识列是表达式的一部分。
- 标识列来自远程数据源。
如果这些条件中的任何一个为真，则将创建 NOT NULL 列，而不是继承 IDENTITY 属性。如果新表中需要标识列但此类列不可用，或者您需要与源标识列不同的种子或增量值，请使用 IDENTITY 函数在选择列表中定义该列。请参阅下面示例部分中的“使用 IDENTITY 函数创建标识列”。
All credit goes to Eric Humphreyand bernd_k
所有功劳都归功于Eric Humphrey和bernd_k
回答by Jasmine
set identity_insert on
Use this.
用这个。
回答by Cade Roux
Not with SELECT *- if you selected every column but the identity, it will be fine. The only way I can see is that you could do this by dynamically building the INSERTstatement.
不是SELECT *- 如果您选择了除标识之外的每一列，那就没问题了。我能看到的唯一方法是您可以通过动态构建INSERT语句来做到这一点。
回答by HLGEM
Just list the colums you want to re-insert, you should never use select * anyway. If you don't want to type them ,just drag them from the object browser (If you expand the table and drag the word, columns, you will get all of them, just delete the id column)
只需列出要重新插入的列，无论如何都不应该使用 select * 。如果您不想输入它们，只需从对象浏览器中拖动它们（如果您展开表格并拖动单词、列，您将获得所有这些，只需删除 id 列）
回答by Rob Packwood
INSERT INTO #Table SELECT MAX(Id) + ROW_NUMBER() OVER(ORDER BY Id)
INSERT INTO #Table SELECT MAX(Id) + ROW_NUMBER() OVER(ORDER BY Id)
回答by BCS
Might an "update where T.ID = #tmp.ID" work?
“在 T.ID = #tmp.ID 的地方更新”是否可行？
回答by Rob Allen
- it gives me a chance to preview the data before I do the insert
- I have joins between temp tables as part of my calculation; temp tables allows me to focus on the exact set data that I am working with. I think that was it. Any suggestions/comments?
- 它让我有机会在插入之前预览数据
- 作为计算的一部分，我在临时表之间进行了连接；临时表使我能够专注于我正在使用的确切集合数据。我想就是这样。任何建议/意见？
For part 1, as mentioned by Kolten in one of the comments, encapsulating your statements in a transaction and adding a parameter to toggle between display and commit will meet your needs. For Part 2, I would needs to see what "calculations" you are attempting. Limiting your data to a temp table may be over complicating the situation.
对于第 1 部分，正如 Kolten 在其中一条评论中提到的，将您的语句封装在事务中并添加一个参数以在显示和提交之间切换将满足您的需求。对于第 2 部分，我需要查看您正在尝试的“计算”。将数据限制在临时表中可能会使情况变得过于复杂。
