create table with_id ( id int identity(1,1),val varchar(30) );
众所周知,这一点
select * into copy_from_with_id_1 from with_id;
导致copy_from_with_id_1也带有id身份.
以下stack overflow question提及明确列出所有列.
我们试试吧
select id,val into copy_from_with_id_2 from with_id;
糟糕,即使在这种情况下,id也是一个标识列.
我想要的是像一张桌子
create table without_id ( id int,val varchar(30) );
解决方法
The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name,data type,nullability,and value as the corresponding expression in the select list. The IDENTITY property of a column is transferred except under the conditions defined in “Working with Identity Columns” in the Remarks section.
在页面下方：
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.
那么……你理论上可以逃脱：
select id,val into copy_from_with_id_2 from with_id union all select 0,'test_row' where 1 = 0;
