SQL Bulk Insert in C# when you have GUID (UniqueIdentifier) Column

Question

I need to copy data from one table to the other table. I get an exception with message:

The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column",

because I have column with GUID datatype.

My code...

using (SqlConnection destinationConn = new SqlConnection("Data Source = ''; Initial Catalog = ''; Persist Security Info = True; User ID = ''; Password = ''; MultipleActiveResultSets = true"))
            {
                using (SqlBulkCopy bc = new SqlBulkCopy(destinationConn))
                {
                    bc.DestinationTableName = "Animal";

                    destinationConn.Open();

                    bc.ColumnMappings.Add("guid", "guid"); //UniqueIdentifier
                    bc.ColumnMappings.Add("Name", "Name");
                    bc.WriteToServer(dt);  //DATA TABLE object


                }
            }

How can I solve this problem?

The error is pretty clear. You tried to insert a `string` DataTable column to a `uniqueidentifier` table column. The datatable column's type should be Guid — Panagiotis Kanavos, Dec 11 '18 at 08:41
Apart from that, withouth code that reproduces the problem it's impossible to help. Somehow, somewhere, a string column was created. How do you create that DataTable? — Panagiotis Kanavos, Dec 11 '18 at 08:43
Take a look for [this issue](https://stackoverflow.com/questions/13627325/inserting-guids-with-sqlbulkcopy), probably your problem is similar or equally matched with that. — Tetsuya Yamamoto, Dec 11 '18 at 08:44
Are all values in the GUID column valid GUIDs? i.e. Blank values, non-GUID strings, or GUIDs which are formatted in an unexpected way may cause issues. By the last statement, I mean traditionally GUIDs would contain 4 hyphens; if the hyphens are missing SQL may not accept the value. You can optionally wrap the strings in braces, and the values are not case sensitive. — JohnLBevan, Dec 11 '18 at 08:54

malik masis · Accepted Answer · 2022-05-17T12:27:57.903

I met with a similar issue and solved it with the following code.

                DataTable dt = new DataTable();
                dt.Columns.Add("IdColumn1", typeof(Guid));
                dt.Columns.Add("IdColumn2", typeof(Guid));
                foreach (var item in MyCollection)
                    dt.Rows.Add(item.obj1, item.obj2);

                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    con.Open();

                    using (var bulkCopy = new SqlBulkCopy(con))
                    {
                        bulkCopy.DestinationTableName = "TableName";
                        bulkCopy.ColumnMappings.Add("IdColumn1", "IdColumn1");
                        bulkCopy.ColumnMappings.Add("IdColumn2", "IdColumn2");
                        bulkCopy.WriteToServer(dt);
                    }
                    dt.Dispose();
                }

SQL Bulk Insert in C# when you have GUID (UniqueIdentifier) Column

1 Answers1