T-SQL插入到表中，无需指定每一列。

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 EXCEPT the primary key column from an identical table in a different DB.

在我们的db中，有一个表有超过80列。它有一个主键，身份插入被打开。我正在寻找一种方法，将除主键列之外的所有列插入到这个表中。主键列来自同一个表，位于不同的DB中。

Is this possible?

这是可能的吗?

7 个解决方案

#1

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”中有身份插入，并且列是完全相同的，那么您将不会有问题。

#2

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工作

#3

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#来执行操作，那么它就不会那么麻烦了。

#4

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 server不会接受*。必须在脚本中明确包含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中右键单击该表，让它生成插入和选择脚本，并对它们进行一些编辑，让它们一起工作。

#5

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
无需修改SPROC
Add/change/delete fields easy
添加/更改/删除字段容易
No need to query meta-data
不需要查询元数据
No temporary tables
没有临时表

#6

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.

实际上，从对象浏览器中拖出所有列并从列表中删除标识列需要10秒或更短的时间。除了快速的临时查询外，使用select *是一个坏主意。

#7

-1

In answer to a related question (SELECT * EXCEPT), I point out the truly relational language Tutorial D allows 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 INSERT syntax requires tuple value constructors to include attribute name / value pairs e.g.

但是，它的插入语法要求元组值构造函数包含属性名/值对，例如。

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 VALUES row 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.

一旦指定了列的逗号，值行构造函数就保持了这个顺序，这不是理想的。但至少顺序是指定的:您的提议将依赖于一些可能不确定的默认顺序。

#1