Search code examples
c#sqlimportimport-from-excelsmartxls

Import modified data in Excel to Database Table

I'm working on the function Import, i have an excel file which contains some data, that later will be edited by the user, I managed to do the import excel by SmartXLS in C# and update all data to SQL Server Database, however, what I did is to fetch all data in excel file and update all rows into the SQL Table, which affects to the performance and I also updated unedited rows.

I would like to ask that is there any way that I can get only modified cells, rows in Excel and update to the correponding data in SQL Table?

var workbook = new WorkBook();
workbook.read(filePath);
var dataTable = workbook.ExportDataTable();
Solution

  • Just a Scenarion, maybe it helps you to understand what gordatron and i were talking about:

    Following Situation: There is a Table "Products" wich is central storage place for product informations and a table "UpdatedProducts" which structure looks exactly like "Products" table but data maybe different. Think of following scenarion: you export product table to excel in the morning. the whole day you delete, add, update products in your excel table. At the end of the day you want to re-import your excel data to "Products" table. What you need:

    • delete all records from "UpdatedProducts"
    • insert data from excel to "UpdatedProducts" (bulk insert if possible)
    • update the "Products" table

    Then a Merge-Statement could look like this:

    MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
    ON TARGET.ProductID = SOURCE.ProductID
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate 
    THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
                    TARGET.Rate = SOURCE.Rate 
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (ProductID, ProductName, Rate) 
        VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE

    What this Statement does: WHEN MATCHED: Data exist in both tables, we update data in "Products" if ProductName or Rate is different

    WHEN NOT MATCHED BY TARGET: Data exist in staging table but not in your original table, we add them to "Products"

    WHEN NOT MATCHED BY SOURCE: Data exists in your original table but not in staging table, thy will be deleted from "Products"

    Thanks a lot to http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/ for this perfect example!