Thoughts from Dan Miser RSS 2.0
# Tuesday, February 3, 2009

I'm using SqlBulkCopy to quickly copy an Excel spreadsheet into MSSQL. It is lightning fast, and works extremely well. One snag came up because I need to have an Identity field for my primary key on this newly copied table (the short story is NHibernate's need for a PK, and the lack of anything resembling a PK in the Excel data due to it coming from legacy data).

The problem is if you add the Identity field in the first column of the destination table, SqlBulkCopy will try to line the columns up by position and things break. You can add ColumnMappings to have SqlBulkCopy write the excel columns to the right MSSQL table column, but that can be a lot of code if you have a lot of columns.

My solution ended up being to simply add the Identity field to the end of the destination table. By doing that, the columns line up during the SqlBulkCopy and everything works just fine.

Tuesday, February 3, 2009 4:13:23 AM (GMT Standard Time, UTC+00:00)  #    Comments [0] -
ADO.NET
Navigation
Archive
<December 2014>
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910
Blogroll
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2014
Dan Miser
Sign In
Statistics
Total Posts: 388
This Year: 0
This Month: 0
This Week: 0
Comments: 630
Themes
Pick a theme:
All Content © 2014, Dan Miser
DasBlog theme 'Business' created by Christoph De Baene (delarou)