Synchronizing between SQL Server and CSV file

Question

What is the better way to synchronize a table (customers), inside SQL Server and the curtomers inside an CSV file in Microsoft Excel?

Ok, here is the explanation: I am developing a software in C#.NET 2008, and I create a table named Customers in SQL Server 2005. The contents of this table comes from a csv file and the user can add more information because the SQL Table has more fields than csv file.

The first time is easy.. I just ADDNEW for each line in csv file. But, the second time I cannot delete all table to import it again from the beginning because of these extra fields, so I need a method that can verify each record inside my SQL Table and CSV file automatically? Or I need to treat the records one by one?

Answer 1

Take a look at SQL Server Integration Services, it can do everything you want to do and way more.

Alternatively, if you do want to code it, then I suggest making a hash of all fields in the CSV and using that to compare each row. Different hash = change = updated row.

Answer 2

You could separate the CSV table and the extra information into one table and one view. You would have one table that is just a dumb mirror of the CSV file itself, and is easy to update:

    TRUNCATE TABLE csvmirror;
    BULK INSERT csvmirror
    FROM 'P:\ath\to\csvfile.csv' WITH
    ( FIELDTERMINATOR = ','
    , ROWTERMINATOR = '\n' );

Then you create a view on that 'csvmirror' table that supplements the direct csv-file data with your extra data from the database.