Best method to do bulk update (.NET)

[invidia]

I'm trying to find the most efficient method of updating a SQL Server table from a .csv file. I don't think I can do a direct .csv -> SQL Server import (or can I? Not too familiar with T-SQL programming), because the .csv file structure needs to go through some modifications, ie removing/adding columns, filtering out some data, etc. I was thinking of doing this through VB.NET/C#.

The .csv range from 20-30 columns and +500,000 rows. I'm looking to replace or update tables with this data and not add more to it (insert).

 

[Train]

There's a million ways to skin that cat.

Do you have SQL Management Studio? The built in Import/Export wizards are pretty good.

Usually in a case like yours I would just do a raw import to a new table, do the needed massaging there, then copy over to the destination table. delete the import table afterwards if you want.

 

[GeekDrew]

Is this one-time or recurring?

 

[Markbnj]

Yeah, what Train said. The Data Import/Export wizard in SQL Management studio is very powerful, and it can handle really large files. I've used it to import flat files with over a million rows. It has some quirks and getting the column types set up correctly can be a pain sometimes, but it works. And then GeekDrew's question is a very good one as well, because if this is recurring other solutions might make more sense (i.e. write a small process to import the rows into a temp table and then kick off a stored proc to finalize it, and schedule the program).

 

[linkgoron]

You could also look at SSIS (sql server integartion services). I've used it more than once for importing and exporting.

 

[invidia]

Is this one-time or recurring?

It's recurring. I'll take a look at the importing functionality of the management tool. I haven't used any other features of SQL Server Management tool except for creating DBs and doing queries.

 

[pcgeek101]

This sounds like a job for Windows PowerShell. Look at the Import-CSV cmdlet, and there are plenty of code samples out there for talking to SQL Server.

Cheers,
Trevor Sullivan
http://trevorsullivan.net

 

[Train]

It's recurring. I'll take a look at the importing functionality of the management tool. I haven't used any other features of SQL Server Management tool except for creating DBs and doing queries.

One nice thing about the import wizard is that once you are done setting up your import, you can save it as a DTS package, so you won't have to go through the trouble of configuring it the next time you want to use it.