CODEWITHSUNDEEP
sql-serverdatabasesql-updateunionalter-table
S.Perera
S.Perera

Reputation: 894

SQL Server append table to an existing table

I need to append rows from one table to an existing table. I am thinking something like following but this gives an error

select * 
into table1
from (select * 
      from table1
      union
      select *  
      from table2) as tmp

Is there a way to use ALTER TABLE or UPDATE with UNION?

Upvotes: 1

Views: 20392

Answers (3)

Ali
Ali

Reputation: 1200

i will assume the below scenario ,

1- you need to insert in table1 all data from table 2 use this one 

 INSERT INTO TABLE1 (Col1, Col2)
 SELECT Col1, COl2  FROM  Table2

2- you have 2 tables , table 1 and need to insert in table 3

INSERT INTO TABLE3 (Col1, Col2)
SELECT  Col1, COl2  FROM Table1
Union all --to remove duplication in data 
SELECT  Col1, COl2  FROM Table2

Upvotes: 7

Hasan Fathi
Hasan Fathi

Reputation: 6086

Use this query:

   -- merge table1 and table2 data in temp table
    SELECT * INTO #Temp
      FROM 
          (
            SELECT * 
              FROM table1
             UNION
            SELECT *  
              FROM table2
          )

    -- empty table1 and delete existing data
    TRUNCATE TABLE table1

    -- insert all merged data in table 1
    INSERT INTO table1 
                SELECT *
                  FROM #Temp

You can remove duplicate rows with using UNION ALL instead of UNION

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Just do a direct insert to Table1 from Table2

INSERT INTO TABLE1 (Col1, Col2)
    SELECT
        Col1, COl2
    FROM 
        Table2

Upvotes: 2

Related Questions