Insert records that don't exists in the table

Question

Post reply

Insert records that don't exists in the table

Krasavita

SSC-Insane

Points: 23138
More actions
April 23, 2009 at 11:47 am

#155598

How to insert records. I have table1 and table2
Table 1 have records, i need to insert records from table 1 to table 2, if recods are equal between 2 tables not to insert, only insert records that are not in table 2. Thank you

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

-- build a CTE of the rows that need to be added (not already in Table2)

;with CTE AS

(

select column1, column2 from Table1 -- use the columns that determine uniqueness

EXCEPT

select column1, column2 from Table2 -- use the columns that determine uniqueness

)

-- now insert all of those that are missing from Table2

insert into Table2

select * from Table1

where Column1 = CTE.column1 and column2 = CTE.column2

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

... how about something like this?

insert into table2

select * from table1

except

select * from table2

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WayneS SSC Guru Points: 95461 More actions · Answer 3

PaulB (4/23/2009)
... how about something like this?
insert into table2
select * from table1
except
select * from table2

This would work IF all of the columns are used to create the uniqueness. Usually, this isn't the case. You would use the PK. So if you ran this, you could come up with a row that was not in table2, but the column(s) making up the PK are, creating an error.

I like your thinking...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

:w00t: hey!...

I'm following the given business specs here, look how they read: "...if recods are equal between 2 tables not to insert, only insert records that are not in table 2"

😀

By the way, if business rules are not correct -shocking, isn't it? - poster can always add a where clause on both the right and left sides of the except operator 😉 ... I would do it but I'll have to charge the poster a couple of extra bucks.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.