T-SQL 插入表而不必指定每一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9573639/
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
T-SQL Insert into table without having to specify every column
提问by Eric
In our db there is a table that has a little over 80 columns. It has a primary key and Identity insert is turned on. I'm looking for a way to insert into this table every column EXCEPTthe primary key column from an identical table in a different DB.
在我们的数据库中有一个有 80 多列的表。它有一个主键并打开了身份插入。我正在寻找一种方法将每个列插入到这个表中，除了来自不同数据库中相同表的主键列。
Is this possible?
这可能吗？
回答by Ta01
You can do this quite easily actually:
实际上，您可以很容易地做到这一点：
-- Select everything into temp table
Select * Into
#tmpBigTable
From [YourBigTable]
-- Drop the Primary Key Column from the temp table
Alter Table #tmpBigTable Drop Column [PrimaryKeyColumn]
-- Insert that into your other big table
Insert Into [YourOtherBigTable]
Select * From #tmpBigTable
-- Drop the temp table you created
Drop Table #tmpBigTable
Provided you have Identity Insert On in "YourOtherBigTable" and columns are absolutely identical you will be okay.
如果您在“YourOtherBigTable”中有 Identity Insert On 并且列完全相同，您就可以了。
回答by Kibbee
You could query Information_Schema to get a list of all the columns and programatically generate the column names for your query. If you're doing this all in t-sql it would be cumbersome, but it could be done. If you're using some other client language, like C# to do the operation, it would be a little less cumbersome.
您可以查询 Information_Schema 以获取所有列的列表，并以编程方式为您的查询生成列名。如果你在 t-sql 中做这一切会很麻烦，但它可以做到。如果您使用其他一些客户端语言，例如 C# 来执行操作，那么麻烦会少一些。
回答by Wim
No, that's not possible. You could be tempted to use
不，那不可能。你可能会想使用
INSERT INTO MyLargeTable SELECT * FROM OtherTable
But that would not work, because your identity column would be included in the *.
但这不起作用，因为您的身份列将包含在 *.
You could use
你可以用
SET IDENTITY_INSERT MyLargeTable ON
INSERT INTO MyLargeTable SELECT * FROM OtherTable
SET IDENTITY_INSERT MyLargeTable OFF
first you enable inserting identity values, than you copy the records, then you enable the identity column again.
首先启用插入标识值，然后复制记录，然后再次启用标识列。
But this won't work neither. SQL server won't accept the * in this case. You have to explicitly include the Id in the script, like :
但这也行不通。在这种情况下，SQL 服务器将不接受 *。您必须在脚本中明确包含 Id，例如：
SET IDENTITY_INSERT MyLargeTable ON
INSERT INTO MyLargeTable (Id, co1, col2, ...., col80) SELECT Id, co1, col2, ...., col80 FROM OtherTable
SET IDENTITY_INSERT MyLargeTable OFF
So we're back from where we started.
所以我们从我们开始的地方回来了。
The easiest way is to right click the table in Management Studio, let it generate the INSERT and SELECT scripts, and edit them a little to let them work together.
最简单的方法是在 Management Studio 中右键单击该表，让它生成 INSERT 和 SELECT 脚本，并稍微编辑它们以让它们协同工作。
回答by Dave Mateer
CREATE TABLE Tests
(
TestID int IDENTITY PRIMARY KEY,
A int,
B int,
C int
)
INSERT INTO dbo.Tests
VALUES (1,2,3)
SELECT * FROM Tests
This works in SQL2012
这适用于 SQL2012
回答by davidWazy
Why not just create a VIEW of the original data, removing the unwanted fields? Then 'Select * into' your hearts desire.
为什么不创建原始数据的视图，删除不需要的字段？然后'选择*进入'你心中的愿望。
- Localized control within a single view
- No need to modify SPROC
- Add/change/delete fields easy
- No need to query meta-data
- No temporary tables
- 单一视图中的本地化控制
- 无需修改SPROC
- 轻松添加/更改/删除字段
- 无需查询元数据
- 没有临时表
回答by HLGEM
Really, honestly it takes ten seconds or less to pull all of the columns over from the object browser and then delete the identity column from the list. It is a bad idea to use select * for anything but quick ad hoc query.
真的，老实说，从对象浏览器中拉出所有列，然后从列表中删除标识列需要十秒钟或更短的时间。将 select * 用于除快速临时查询之外的任何事情都是一个坏主意。
回答by onedaywhen
In answer to a related question (SELECT * EXCEPT), I point out the truly relational language Tutorial Dallows projection to be expressed in terms of the attributes to be removed instead of the ones to be kept e.g.
在回答相关问题（SELECT * EXCEPT）时，我指出真正的关系语言教程 D允许根据要删除的属性而不是要保留的属性来表达投影，例如
my_relvar { ALL BUT description }
However its INSERTsyntax requires tuple value constructors to include attribute name / value pairs e.g.
然而，它的INSERT语法需要元组值构造函数来包含属性名称/值对，例如
INSERT P
RELATION
{
TUPLE { PNO PNO ( 'P1' ) , PNAME CHARACTER ( 'Nut' ) },
TUPLE { PNO PNO ( 'P2' ) , PNAME CHARACTER ( 'Bolt' ) }
};
Of course, using this syntax there is no column ordering (because it is truly relational!) e.g. this is semantically equivalent:
当然，使用这种语法没有列排序（因为它是真正的关系！）例如，这在语义上是等效的：
INSERT P
RELATION
{
TUPLE { PNO PNO ( 'P1' ) , PNAME CHARACTER ( 'Nut' ) },
TUPLE { PNAME CHARACTER ( 'Bolt' ) , PNO PNO ( 'P2' ) }
};
The alternative would be to rely fully on attribute ordering, which SQL does partially e.g. this is a close SQL equivalent to the the above:
另一种方法是完全依赖于属性排序，SQL 部分地这样做，例如，这是一个与上述等效的接近 SQL：
INSERT INTO P ( PNO , PNAME )
VALUES
( PNO ( 'P1' ) , CAST ( 'Nut' AS VARCHAR ( 20 ) ) ) ,
( PNO ( 'P2' ) , CAST ( 'Bolt' AS VARCHAR ( 20 ) ) );
Once the commalist of columns has been specified the VALUESrow constructors have the maintain this order, which is not ideal. But at least the order is specified: your proposal would rely on some default order which may be possibly non-deterministic.
一旦指定了VALUES列的逗号列表，行构造函数就会保持这个顺序，这并不理想。但至少指定了顺序：您的提案将依赖于一些可能不确定的默认顺序。
