SQL Server 2008 - Inserting Rows in One Table From Another Table

I have two identical tables in my SQL Server database (format is identical not data content). Table B has a number of rows in it that do not appear in Table B. My goal is to move those rows from Table B into Table A. Will the following SQL work for that or do I need to take a different approach?

Insert Into TableA
Select * from TableB
Left Outer Join TableA
On TableA.Column1 = TableB.Column1 And TableA.Column2 = TableB.Column2
Where TableA.Column 1 is null

Let me know if this SQL will work for that or if I need to try something different.

Thanks, in advance, for your help.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cbridgmanAuthor Commented:
My initial posting has an error in it. In it, I said that "Table B has a number of rows in it that do not appear in Table B". What I meant to say was that "Table B has a number of rows in it that do not appear in Table A".
You can use except:

insert into A
select * from B
select * from A

If the tables have columns that are not the same, i.e. identity columns then instead of * you should use columns list in both selects and in insert as well:

insert into A (colx, coly...)
select colx, coly... from B
select colx, coly... from A

If you want to sync the 2 tables, which means to insert/update/delete to have them the same after your operation then the best choice is MERGE statement described here:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cbridgmanAuthor Commented:
Thanks Zberteoc,

The two tables are identical in terms of structure ... Identical column names, types, etc. Even the data is identical for the most part. There are, however, somewhere in the neighborhood of 1500 rows that exist in TableB but do not exist in TableA. That's about 10 percent of the total rows in TableB. Anyway, all I need to do is take those rows from TableB and copy them into TableA.

I've never used the "except" before. I will give it a try though.
Except will return all the rows from the top table that don't exist in the bottom table. There is also intersect:

select * from B
select * from A

which returns all the rows that exist in BOTH tables.

The comparison is done column by column and the column names don't matter. All that matters is that the 2 selects will return the same number of columns of the same or convertible types in the same position.
Deepak ChauhanSQL Server DBACommented:
You can insert difference data using like this.

Insert into TableA
Select * from TableB where
Column1 not in (Select Coumn1 from TableA)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.