0

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();
pnuts
  • 58,317
  • 11
  • 87
  • 139
Blue Cross
  • 73
  • 12
  • 1
    Can you upload to a staging table then update changes within the DB? - sorry not 100% sure where the performance bottle neck is. excel reading rows? inserting to the DB or checking which lines have changed (if so where is this done - is it between datatables in the client application?) –  May 28 '15 at 13:45
  • those questions don't need answered.. they are just there to explain my suggestion ;) –  May 28 '15 at 13:47
  • Updated rows since when? – artm May 28 '15 at 13:48
  • Thanks, Not much about the performace problem as now I just have some excel rows, but later we will have hundred or more rows, what I believe that the performance will be reduced, and also, I personally think that it is not so effective if I also get unmodified rows in excel and do the update sql of these data. @gordatron . Sorry I could not get the idea of the staging table ? – Blue Cross May 28 '15 at 13:50
  • 1
    @gordatron that's what i would recommend too. Pull the data from excel to a staging table (maybe you can bulk insert the data - this would be the fastest solution) and then do a Merge-Statement to your Data on SQL-Server – CeOnSql May 28 '15 at 13:50
  • bulk insert is much MUCH faster than row by row.. but if you are using datatables you may already be doing that. I can also verify that excel is very slow at reading row by row. try an import from SSMS of the whole file into a staging table to get a feel for how fast it could be. as for original question i would investigate programable access to "track changes" functionality in excel. –  May 28 '15 at 13:55
  • have a look at this, i think this example is perfect for your problem: http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/ – CeOnSql May 28 '15 at 13:58

1 Answers1

1

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!

CeOnSql
  • 2,615
  • 1
  • 16
  • 38
  • Thank you, @CeOnSql it is a clear answer, I could grab what you guys suggested, now what i'm doing is I will pass a list of data collected in excel file, to SQL under TVP parameters, but I still wonder how can I get data in TVP (productname, rate, etc) to insert into the UpdatedProducts table – Blue Cross May 28 '15 at 15:05
  • Or I should use Bulk Insert but im not familiar with this approach – Blue Cross May 28 '15 at 15:06
  • I think the slowest operation in this case is to read the data from excel and store it in a DataTable in C#. If you are able to transform your excel to a csv file then bulk insert would be easy. Here is a description how to save a xls(x) File to csv: http://stackoverflow.com/questions/2536181/is-there-any-simple-way-to-convert-xls-file-to-csv-file-excel and here you can find how to bulk insert this temporary csv: http://stackoverflow.com/questions/19302226/import-excel-to-sql-server-2008 – CeOnSql May 29 '15 at 06:30
  • And to be sure that Performance of MERGE Statement is OK you should have a (unique) clustered index on ProductID and nonclustered index on ProductName and Rate on BOTH TABLES! – CeOnSql May 29 '15 at 06:31
  • Thanks a lot @CeOnSql, I have just one more thing, is the <> symbol works on the comparison between 2 strings ? and it ignores case or I have to manage the Case Sensitive ? – Blue Cross May 29 '15 at 07:05
  • <> is correct on sql server for strings and numbers,... If case matters belongs to the collation of the server, database or even of the column. A typical collation is "SQL_Latin1_General_CP1_CI_AS" where CI stands for CaseInsensitive. So Case doesn't matter. If you have "SQL_Latin1_General_CP1_CS_AS" your server/database/column is case-sensitive. Check this out - thats how you can find out what collation you have to deal with: https://msdn.microsoft.com/en-us/library/hh230914.aspx?f=255&MSPPError=-2147217396 – CeOnSql May 29 '15 at 07:12