SqlBulkCopy Not Working
asp.net, c#, sqlbulkcopy
Solution
Well, is it right? Do the column names exist on both sides?
To be honest, I've never bothered with mappings. I like to keep things simple - I tend to have a staging table that looks like the input on the server, then I `SqlBulkCopy` into the staging table, and finally run a stored procedure to move the table from the staging table into the actual table; advantages:
- no issues with live data corruption if the import fails at any point
- I can put a transaction just around the SPROC
- I can have the bcp work without logging, safe in the knowledge that the SPROC will be logged
- it is simple ;-p (no messing with mappings)
As a final thought - if you are dealing with bulk data, you can get better throughput using `IDataReader` (since this is a streaming API, where-as `DataTable` is a buffered API). For example, I tend to hook CSV imports up using CsvReader as the source for a SqlBulkCopy. Alternatively, I have written shims around `XmlReader` to present each first-level element as a row in an `IDataReader` - very fast.
Problem
I have a `DataSet` populated from Excel Sheet. I wanted to use SQLBulk Copy to Insert Records in `Lead_Hdr` table where `LeadId` is PK. I am having following error while executing the code below: The given ColumnMapping does not match up with any column in the source or destination ``` string ConStr=ConfigurationManager.ConnectionStrings["ConStr"].ToString(); using (SqlBulkCopy s = new SqlBulkCopy(ConStr,SqlBulkCopyOptions.KeepIdentity)) { if (MySql.State==ConnectionState.Closed) { MySql.Open(); } s.DestinationTableName = "PCRM_Lead_Hdr"; s.NotifyAfter = 10000; #region Comment s.ColumnMappings.Clear(); #region ColumnMapping s.ColumnMappings.Add("ClientID", "ClientID"); s.ColumnMappings.Add("LeadID", "LeadID"); s.ColumnMappings.Add("Company_Name", "Company_Name"); s.ColumnMappings.Add("Website", "Website"); s.ColumnMappings.Add("EmployeeCount", "EmployeeCount"); s.ColumnMappings.Add("Revenue", "Revenue"); s.ColumnMappings.Add("Address", "Address"); s.ColumnMappings.Add("City", "City"); s.ColumnMappings.Add("State", "State"); s.ColumnMappings.Add("ZipCode", "ZipCode"); s.ColumnMappings.Add("CountryId", "CountryId"); s.ColumnMappings.Add("Phone", "Phone"); s.ColumnMappings.Add("Fax", "Fax"); s.ColumnMappings.Add("TimeZone", "TimeZone"); s.ColumnMappings.Add("SicNo", "SicNo"); s.ColumnMappings.Add("SicDesc", "SicDesc"); s.ColumnMappings.Add("SourceID", "SourceID"); s.ColumnMappings.Add("ResearchAnalysis", "ResearchAnalysis"); s.ColumnMappings.Add("BasketID", "BasketID"); s.ColumnMappings.Add("PipeLineStatusId", "PipeLineStatusId"); s.ColumnMappings.Add("SurveyId", "SurveyId"); s.ColumnMappings.Add("NextCallDate", "NextCallDate"); s.ColumnMappings.Add("CurrentRecStatus", "CurrentRecStatus"); s.ColumnMappings.Add("AssignedUserId", "AssignedUserId"); s.ColumnMappings.Add("AssignedDate", "AssignedDate"); s.ColumnMappings.Add("ToValueAmt", "ToValueAmt"); s.ColumnMappings.Add("Remove", "Remove"); s.ColumnMappings.Add("Release", "Release"); s.ColumnMappings.Add("Insert_Date", "Insert_Date"); s.ColumnMappings.Add("Insert_By", "Insert_By"); s.ColumnMappings.Add("Updated_Date", "Updated_Date"); s.ColumnMappings.Add("Updated_By", "Updated_By"); #endregion #endregion s.WriteToServer(sourceTable); s.Close(); MySql.Close(); } ```