Insert into a table from another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert into a table from another table

Author

Topic

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-01-22 : 10:43:16

Hi all,

I have table called Table1. I want to insert data into this table from Table2 but only the rows that are not already in Table1.

Both tables have the exact same structure/column names.

I'm guessing this requires a join of some type.

Let's say the table has the following structure

Table 1

Name Age Address
Dave....21...81 Grove
Mike.....30...52 Vauxhall Road

Table 2

Name Age Address
Dave....21...81 Grove
Mike.....30...52 Vauxhall Road
Dave....23...77 Temple Avenue
Ella......24...22 Bridge Street
Anne....35...55 London Road

I want to copy only the bottom three records from Table 2 into Table 1 since they do not exist in Table 1

The primary key would be a composite key with all three columns.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-01-22 : 11:06:50

[code]
INSERT INTO Table1
SELECT *
FROM Table2
EXCEPT
SELECT *
FROM Table1;
[/code]

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-01-22 : 14:59:57

Many thanks Ifor, it worked perfectly.

Do you know if the performance of doing the EXCEPT is better than doing a left join?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources