T-SQL 插入表而不必指定每一列 [英] T-SQL Insert into table without having to specify every column

问题描述

在我们的数据库中有一个有 80 多列的表.它有一个主键并打开了身份插入.我正在寻找一种方法来将每一列插入到该表中EXCEPT 来自不同数据库中相同表的主键列.

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.

这可能吗?

推荐答案

实际上你可以很容易地做到这一点:

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

如果您在YourOtherBigTable"中有 Identity Insert On 并且列完全相同，您就可以了.

Provided you have Identity Insert On in "YourOtherBigTable" and columns are absolutely identical you will be okay.

这篇关于T-SQL 插入表而不必指定每一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！