Update records from CVS file or drop all table?

Question

Hi all

well this is the first time in this forum so I'II be clear about my question and thanks in advance

I'll create a software in C#, this app takes a CSV file (about 73,000 lines). This files comes from another system, also the CSV file can change, what's that means? the CSV file can change in one line ( the data ) or can have more new lines or can have less lines, the CSV change every hour or half hour it depends!!!

I add those lines from the CSV file to a table in SQL,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, or in other case if a line in the CSV change, i can't drop all the data from the table.

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? If i compare every change it would be correct to use a cursor fot that?

Thanks

Answer 1

There's not a simple answer to this; it's a process.

First, what version of SQL Server are you using? If you're using SQL Server 2008, you have access to the MERGE T-SQL command, which can allow you to fashion an "upsert" command (UPDATE or INSERT depending on the presence of data). If you're using something older, the coding gets a little more complex, but not impossible.

Second, when you load your data, always load into a staging table first. This allows you to do set-based comparisons, and "upsert" as necessary. In 99.999% of the cases, you should be able to do this without a cursor, and have it perform better.

Stu

Answer 2

Hi stu

well I'm using sql server 2005, so i think that maybe the option for 2008 that you said isn't available...but i wll chek to know what's your idea and to know more about sql server 2008 thanks for the tip.

Second: in the past i used this from c#(desk app) to upload my data:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn, SqlBulkCopyOptions.TableLock, null)) { bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "BomCleanTest"; dtDest = CreateDataTableFromFile(); bulkCopy.BatchSize = dtDest.Rows.Count; try { bulkCopy.ColumnMappings.Add(0, 1); bulkCopy.ColumnMappings.Add(1, 2); bulkCopy.ColumnMappings.Add(2, 3); bulkCopy.ColumnMappings.Add(3, 4); bulkCopy.ColumnMappings.Add(4, 5); bulkCopy.ColumnMappings.Add(5, 6); bulkCopy.WriteToServer(dtDest); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }

i read each line, seprate by coma, and add to my datatable and then i used the bulkcopy, with that i load all the data for first time.... this sometimes takes a lot cpu usage.

Now the problem is when the user add new lines into my cvs file, and modified couple more, what's the best way..because i think that truncate the sql table each time when the user upload the cvs file is not a good idea, i think to do this...

1.- create a temp table, then upload the cvs file ( this is the second time that the user need to upload the data) 2.- with a cursor one by one record ask if exist in the final table(the final table is where the user see the data into a web page grid for example) and if exist update not matters if change or not, and use a extra field named checked...

buti dont know if a cursor is a good idea like you said, if you have a clue or idea cloud you help me please? thanks man and c yaa

Answer 3

If you are already using SSIS to directly load the data and don't want to use a staging table for some reason, and you have some kind of key you can use to order the rows, you can use the TableDifference custom component. Basically, you read both the "old" table and the "new" table into two separate flows, sort them in the same order and pass them through the TableDifference. This generates 3 potential outputs - INSERTs, UPDATEs and DELETEs. INSERTs can usually be handled with a regular insert task, but UPDATEs and DELETEs can either use individual command invocations or can stream to staging tables to batch the UPDATE or DELETE operation.

Answer 4

hey that's a good idea let me check this component and see if i can use for my issue, did you use this ssi before? how about performance ?

Thanks, I'll try to use this in the afternoon and post the result in the night, thanks and if somebody else has another clue let me know, c ya

Answer 5

Hi all, now i solved my problem using table diff ssi, now I'm working with that, thanks to all and c ya later