将表附加到现有表：SQL Server

Append table to an existing one: SQL Server

提问by npereira

I have a table Awith two columns that I want to append to another table B, how can I do that? They have the exact same rows. Software is SQL Server 2012.

我有一个A包含两列的表，我想将其附加到另一个表中B，我该怎么做？它们具有完全相同的行。软件是SQL Server 2012.

EDIT (attempted code from comment):

编辑（来自评论的尝试代码）：

INSERT INTO B_table([Column 0], [Column 1])
    SELECT [Column 0], [Column 1]
    FROM [dbo].[A] ;

采纳答案by Gordon Linoff

The basic form is:

基本形式是：

insert into tableB(col1, col2)
    select col1, col2
    from tableA;

This may not work if, for instance, you have a unique constraint on the columns and the insertviolates this constraint.

例如，如果您对列有唯一约束并且insert违反了此约束，则这可能不起作用。

This assumes that you actually want to add the rows to the table. If you just want to see the results together:

这假设您确实想要将行添加到表中。如果您只想一起查看结果：

select col1, col2 from tableB union all
select col1, col2 from tableA;

EDIT:

编辑：

The goal seems to be to add columns one tableB. You can do this by adding the columns and then updating the values:

目标似乎是添加第一列tableB。您可以通过添加列然后更新值来做到这一点：

alter table tableB add col1 . . . ;
alter table tableB add col2 . . . ;

The . . .is the definition of the column.

该. . .是列的定义。

Then do:

然后做：

update b
    set col1 = a.col1, col2 = b.col2
    from tableb b join
         tablea a
         on b.joinkey = a.joinkey;

If you don't have a column for joining, then you have a problem. Tables in SQL are inherently unordered, so there is no way to assign values from a particular row of Ato a particular row of B.

如果您没有用于加入的列，那么您就有问题了。SQL 中的表本质上是无序的，因此无法将值从 的特定行分配A到 的特定行B。

回答by Chong Lip Phang

Try this:

尝试这个：

INSERT INTO tbl1 SELECT * FROM tbl2;

回答by Charles Bretana

If the new table does not exist yet, you have to create it first, or use the form:

如果新表尚不存在，则必须先创建它，或者使用以下形式：

Select [Column list] Into [NewTableName] From [oldTableName],

Select [Column list] Into [NewTableName] From [oldTableName],

as in

如

Select col1, col2
Into NewTable
From OldTable

回答by Saurabh

Method 1:

方法一：

INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM OtherTable

Method 2:

方法二：

SELECT FirstName, LastName
INTO TestTable
FROM OtherTable

Source.

来源。