• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 62
  • Last Modified:

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.

  • 3
2 Solutions
Ryan ChongCommented:
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
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
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....
Ryan ChongCommented:
do you need further clarification or assistance here?
Ryan ChongCommented:
as suggested
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now