Best way to check for existing record before an insert

Question

Best way to check for existing record before an insert

Viewing 4 posts - 16 through 18 (of 18 total)

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

So the consensus is (please correct me if I'm wrong):

Dump the contents of each file into a staging table, put an index on the column I want to look up against, delete the records from the main table where the column matches the staging table, then insert the contents from the file directly into the main table?

Seems simple enough but wouldn't that be a bugger on performance?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

MyDoggieJessie (4/14/2011)
So the consensus is (please correct me if I'm wrong):
Dump the contents of each file into a staging table, put an index on the column I want to look up against, delete the records from the main table where the column matches the staging table, then insert the contents from the file directly into the main table?
Seems simple enough but wouldn't that be a bugger on performance?

Try pulling a reference set in the millions into the lookup component cache in SSIS. That will hurt performance 🙂

Importing one line into SQL is nothing. The index on the reference set should already be there. If there is a match, why would you delete and then insert? You already have the record, so why bother?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

The main table (with potentially millions of records) exists and has data the the BI team reports against. The flatfiles contain data that may or may not already exist in that main table.

So if an updated record from the flatfile already exists in the main table, it either needs to be deleted (and re-inserted) or updated.

If it doesn't exist, it can happily be inserted into the main table.

This is the crux of what I'm trying to do here :w00t:

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Just do an insert of the flat file in a staging table. There won't be too many records I guess, so that will go fast.

Make sure you have a up-to-date index in the main table on the key column.

I'm not sure what will perform the best, INSERT/UPDATE/DELETE with an EXISTS clause or the MERGE statement.

Maybe use the MERGE statement, as it will perform all task in one single statement. As the join clause of the MERGE statement is covered with the index in the MainTable, the statement should perform OK.

If you have questions on constructing the MERGE statement, let us know.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP