CODEWITHSUNDEEP
sqlsql-serversql-server-2012
npereira
npereira

Reputation: 195

Append table to an existing one: SQL Server

I have a table A with 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.

EDIT (attempted code from comment):

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

Upvotes: 15

Views: 128573

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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 insert violates this constraint.

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:

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 A to a particular row of B.

Upvotes: 15

Charles Bretana
Charles Bretana

Reputation: 146449

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],

as in

Select col1, col2
Into NewTable
From OldTable

Upvotes: 1

Chong Lip Phang
Chong Lip Phang

Reputation: 9279

Try this:

INSERT INTO tbl1 SELECT * FROM tbl2;

Upvotes: 6

Saurabh
Saurabh

Reputation: 73609

Method 1:

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

Method 2:

SELECT FirstName, LastName
INTO TestTable
FROM OtherTable

Source.

Upvotes: 0

Related Questions