udpate rows with cvs file or drop all data from table?

[SanmGuy]

Hi all

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

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

I add those lines from the cvs 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 cvs 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

[sknake]

>>"the second time I cannot delete all table to import it again from the beginning because of these extra fields"
What are the extra fields?

This is going to be a very processor intensive task :)

Depending on what the data looks like and the type of comparisons you need to perform you can load the .csv in to a "load table" on your SQL Server. Once all of the data is loaded you could do your checks for updates, new records, or deleted records -- then update your live table accordingly.

If you need to do this in C# code that is another matter.

Either way you need to ensure you're handling the transaction log on your SQL Server. With this much constant IO if the database is not in simple mode you will run your harddrive out of space very quickly. There may be bulk utilities for this but I don't have any experience with them.

[DdoubleD]

It sounds like you want to merge a "CSV" file with an existing db table. See if this information will help you:
DataTable.Merge

[SanmGuy]

Hi guys

the extra fields are new lines into the cvs file,maybe new items added by the user.

Well i used in the past this code for bulk my data into a sql table, just for bulk the 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());
                }
            }

this takes a lot of cpu usage...but for this case, i only need to read a simple line in the cvs file and bulk those data into my sql table, but my doubt is about what i need to keep in mind when i try to do this:

1- suppost the first time the cvs file was uploaded with (eg) 500 lines ( imagine), in this point is not a problem...all the data is new.
2- a new user add a couple new line to the cvs file and modified anothers, and he need to upload the new cvs file.

in this case i think that is to much cost for my sql server truncate the table each time when the user need to upload the cvs file right?

what you guys think or could you have an idea what a i need to do only for update those lines that changed, and see if the new lines need to be added?, i think that i need to ask for if exist the item, by the way there's a key field in the file.

i saw the data table merge, but is it possible to merge with a sql table? i saw a couple of examples but i didn't see with a sql table only between datatable's

Thanks guys

[DdoubleD]

If I've understood correctly, you need to update the database table to reflect new and updated records from a given CSV file. So, if you can replace the existing table's record instead of the need to update the record, I think the most efficient way to do this would be to:

1) import/insert the CSV file into a temporary table in your database.
2) launch a stored procedure that:
a) deletes those records that already exist in perm table
b) insert all records from the temporary table using only the needed columns

DB updates are generally much slower than the above technique.

[SanmGuy]

Yes exactly, but the issue is that i need to go one record by one just to check if exist in the perm table and update bacause the temp table has changes, the best way is to use a cursor?

If I've understood correctly, you need to update the database table to reflect new and updated records from a given CSV file. So, if you can replace the existing table's record instead of the need to update the record, I think the most efficient way to do this would be to:

1) import/insert the CSV file into a temporary table in your database.
2) launch a stored procedure that:
a) deletes those records that already exist in perm table
b) insert all records from the temporary table using only the needed columns

DB updates are generally much slower than the above technique.

[DdoubleD]

My suggestion was for you to do the bulk of the work in a "stored procedure" in the database after loading a temporary table with CSV. The "stored procedure" would delete all records from perm table where the given key field matches those found in the temp table, then insert ALL records from the temp table into the perm table.

[SanmGuy]

you mean make the bulk not in c#? the bulk could be done in stored procedure?

thanks man

My suggestion was for you to do the bulk of the work in a "stored procedure" in the database after loading a temporary table with CSV. The "stored procedure" would delete all records from perm table where the given key field matches those found in the temp table, then insert ALL records from the temp table into the perm table.

[DdoubleD]

you mean make the bulk not in c#? the bulk could be done in stored procedure?

No problem. So you know, that is just my opinion and it comes from a an Oracle background some years back. There might be other ways that are just as efficient, perhaps more, but what I suggested is based on my experience.

Cheers!

[SanmGuy]

No man!!! excelent to have your opinion, i fact i'm just a newbie, thanks for your opinion (and everybody), i just ask for what's the best way...load from sql directly or using c#?

thanks

No problem. So you know, that is just my opinion and it comes from a an Oracle background some years back. There might be other ways that are just as efficient, perhaps more, but what I suggested is based on my experience.

Cheers!