Doing a bulk insert with C#?

Question

I am trying to do a bulk insert with C#. I saw some simple code for the case when number of columns in source csv is same as destination table. I have a csv file which I want to insert into specific columns of a destination table. In my case, the number of columns in destination is greater than the those in csv file. I want to be able to map csv columns to destination columns. Is that possible with SqlBulkCopy? If not, any other options ?

I am using .NET 3.5 and Visual Studio 2008

Source http://www.codeproject.com/Articles/439843/Handling-BULK-Data-insert-from-CSV-to-SQL-Server

StreamReader file = new StreamReader(bulk_data_filename);
CsvReader csv = new CsvReader(file, true,',');
SqlBulkCopy copy = new SqlBulkCopy(conn);
copy.DestinationTableName = tablename;
copy.WriteToServer(csv);

Why am I doing this - long story. Its actually for SSIS. SQL server and SSIS cannot parse and execute large .SQL files. So, this was my next option. — Steam
– Steam, Commented Oct 30, 2013 at 22:25
possible duplicate of How to use SqlBulkCopyColumnMappingCollection? — ChrisLively
– ChrisLively, Commented Oct 30, 2013 at 22:25
Looks like IDataReader along with SqlBulk copy must be used - blogs.msdn.com/b/anthonybloesch/archive/2013/01/23/… and here - codeproject.com/Articles/16922/SQL-Bulk-Copy-with-C-Net — Steam
– Steam, Commented Oct 30, 2013 at 23:23

Knickedi · Accepted Answer · 2013-10-30 22:29:16Z

1

Is that what you're looking for? SqlBulkCopyColumnMapping MSDN Reference

// Set up the column mappings by name.
SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping("ProductID", "ProdID");
bulkCopy.ColumnMappings.Add(mapID);

answered Oct 30, 2013 at 22:29

Knickedi

8,7873 gold badges46 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Steam Over a year ago

Will this work for moving data from csv file to table ? right now, my csv file has no column names. but it can be added easily.

Knickedi Over a year ago

This class can map to column indexes (instead names) too. Might be worth a try.

Steam Over a year ago

Can you show me what the code will be like if you only want to load a 1 column csv into a 2 column table ? Thanks. I was hoping that SqlBulkCopy is as simple as - set csv source, define mapping somewhere and let SqlBulkBulkCopy do the rest for you. Is it really like that ?

Knickedi Over a year ago

I'd try something like new SqlBulkCopyColumnMapping(0, "MyColumn"). I think the missing column(s) should be a least optional (nullable or with default value). But I have to admit that I used Bulk Copy with tables of the same structure until now (and not with CSV). Just give it a shot, the class will tell you at runtime if it's happy or not.

Steam Over a year ago

I was thinking of modifying and using the code here - codeproject.com/Articles/16922/SQL-Bulk-Copy-with-C-Net . this link uses SQL result set instead of csv file as data source. also, it does not do the mapping. Can you show me how to do these two things in the given code ? Thanks.

|

Collectives™ on Stack Overflow

Doing a bulk insert with C#?

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related