CODEWITHSUNDEEP
sqlsql-servert-sqlsql-updatesql-insert
James2086
James2086

Reputation: 231

Insert unique rows only and update rows that already exist

I have a table called 'Day Zero' which contains a listing_id column, address column and status column. The listing_id is always unique and is an int data type. The status column in every row is Existing

Each day, I get two text files, one called Listings, and the Other called Sold, and I use BULK insert to import the data directly into the table, the data is these two text files contains only a listing_id and address column.

What I'm trying to figure out, is how to import the data from these files into the table with the following logic:

  1. if listing_id exists in day zero file and listings file then do not import new row and change status on existing row to 'updated'

  2. if listing_id does not exist in day zero file and exists in listing_file then insert new row and change status to 'new'

  3. if listing id exists in day zero file and exists in sold_file then do not import new row and change status on existing row to 'sold'

  4. if listing_id does not exist in day zero file and exists in sold_file then insert new row and change status to 'unknown_sale_event'

Upvotes: 0

Views: 338

Answers (1)

GMB
GMB

Reputation: 222402

You could load each file in a separate temporary table, and then implement the logic using SQL Server's merge syntax:

The following query merges data from the LISTINGS file (assuming that the file was loaded in temporary table #listings):

merge day_zero d 
using #listings l on (l.listing_id = d.listing_id)
when matched
    then update set d.status = 'UPDATED'
when not matched by target
    then insert(listing_id, address, status) 
    values(l.listing_id, address, 'NEW')

And here is the equivalent query for the SALES file:

merge day_zero d 
using #sold s on (s.listing_id = d.listing_id)
when matched
    then update set d.status = 'SOLD'
when not matched by target
    then insert(listing_id, address, status) 
    values(s.listing_id, address, 'UNKNOWN_SALE_EVENT')

Upvotes: 1

Related Questions