1

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);
Brian
  • 5,069
  • 7
  • 37
  • 47
Steam
  • 9,368
  • 27
  • 83
  • 122
  • Check this answer http://stackoverflow.com/a/2978440/1036187 – rivarolle Oct 30 '13 at 22:22
  • 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 Oct 30 '13 at 22:25
  • 1
    possible duplicate of [How to use SqlBulkCopyColumnMappingCollection?](http://stackoverflow.com/questions/2978275/how-to-use-sqlbulkcopycolumnmappingcollection) – NotMe Oct 30 '13 at 22:25
  • Looks like IDataReader along with SqlBulk copy must be used - http://blogs.msdn.com/b/anthonybloesch/archive/2013/01/23/bulk-loading-data-with-idatareader-and-sqlbulkcopy.aspx and here - http://www.codeproject.com/Articles/16922/SQL-Bulk-Copy-with-C-Net – Steam Oct 30 '13 at 23:23

1 Answers1

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);
Knickedi
  • 8,742
  • 3
  • 43
  • 45
  • 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. – Steam Oct 30 '13 at 22:42
  • 1
    This class can map to column indexes (instead names) too. Might be worth a try. – Knickedi Oct 30 '13 at 22:51
  • 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 ? – Steam Oct 30 '13 at 23:05
  • 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. – Knickedi Oct 30 '13 at 23:26
  • I was thinking of modifying and using the code here - http://www.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. – Steam Oct 30 '13 at 23:29
  • Do you have a csv or not? What is stopping you from trying? What exactly are you struggling with? If you update your question I'll extend my answer. – Knickedi Oct 31 '13 at 12:24