Import data into database

Question

I need to import data from a file into a Microsoft SQL Server database.

• The file is roughly structured (inconsistent number of fields but the data needed is available in all rows)
• The file can contain up to 600'000 records
• The first field is identifies a record

The import of the data contains updating existing records in the database with the new values or inserting a new record if the record could not be found.

What is the best approach to accomplish that?

(Reading the file line by line, executing a SELECT and UPDATE or INSERT from my .NET Application doesn't truly satisfy me, because this way it processes around 35 records per second, which leads to several hours of processing time...)

A first option might be to combine the select and update/insert commands in a stored procedure, but this would still take a lot of time.

Is there something like MySQL INSERT ... ON DUPLICATE UPDATE?

Is it possible to pass a list/array of records (multiple columns) to a stored procedure?

If yes how is that implemented?

Thank you for your help :)

Answer 1

You should have a look at SQL Server BULK INSERT to bulk load a file into a staging table in SQL Server.

Once you've loaded the data into a staging table, you can then use the SQL Server 2008 MERGE statement to insert/update ("upsert") your actual data tables. That's exactly the scenario it's designed to solve...

Answer 2

SSIS was created to do exactly that. Here is an example.