MSSQL 2008: Delete rows that exist then insert new ones.

hi all.  I need to compare source_table with target_table.  if target table has same rows as source then delete the those rows from target, and then insert the new  rows from source into target.  What is the best way to do this?  I tried merge but I am having issues.

thanks!
kouts1Asked:
Who is Participating?

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

x
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
What is the best way to do this?  I tried merge but I am having issues.
conventional way will be delete the ones that exists in the target table and then doing the insert from the source table.

OR another way is to update the existing records first and then using a left join statement for the rest of insertion (for new records) from the source table.

you need to compare with the unique identifier in your target table for such operations
0
arnoldCommented:
delete from target_table where target_table.idcolumn in (select id from source_table)
If you have different identifying requirements, you can combine them in the where clause to
but try using select first to confirm
i.e.
select * from target_table where target_table.idcolumn in (select id from source_table)

Is there something else that adds rows to the target_table that you would not want deleted to Ryan's suggestion.

if you have a need to maintain the target_table synced with data from the source_table, add triggers to the source table that will update the target_table ......
i.e. new record is added to the source table, on insert, add the requisite information into the target_table.
similarly on update, update the respective columns
on delete, delete the entry from the target_table....
0

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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
do you need further clarification or assistance here?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
as suggested
0
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.