Error inserting data using SqlBulkCopy

Question

I'm trying to batch insert data into SQL 2008 using SqlBulkCopy.

Here is my table:

IF OBJECT_ID(N'statement', N'U') IS NOT NULL
DROP TABLE [statement]
GO
CREATE TABLE [statement](
 [ID] INT IDENTITY(1, 1) NOT NULL,
 [date] DATE NOT NULL DEFAULT GETDATE(),
 [amount] DECIMAL(14,2) NOT NULL,
CONSTRAINT [PK_statement] PRIMARY KEY CLUSTERED
(
 [ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Here is my code:

private DataTable GetTable()
{
 var list = new List<DataColumn>();
 list.Add(new DataColumn("amount", typeof(SqlDecimal)));
 list.Add(new DataColumn("date", typeof(SqlDateTime)));
 var table = new DataTable("statement");
 table.Columns.AddRange(list.ToArray());
 var row = table.NewRow();
 row["amount"] = (SqlDecimal)myObj.Amount; // decimal Amount { get; set; }
 row["date"] = (SqlDateTime)myObj.Date; // DateTime Date { get; set }
 table.Rows.Add(row);
 return table;
}
private void WriteData()
{
 using (var bulk = new SqlBulkCopy(strConnection, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
 {
 //table.Columns.ForEach(c => bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)));
 bulk.BatchSize = 25;
 bulk.DestinationTableName = "statement";
 bulk.WriteToServer(GetTable()); // a table from GetTable() 
 }
}

So I'm getting error:

The given value of type SqlDateTime from the data source cannot be converted to type date of the specified target column.

Why?? How can I fix that? Help me, please!

Answer 1

Using your original table script, the following code works.

private static DataTable GetTable()
{
 var list = new List<DataColumn>();
 list.Add(new DataColumn("amount", typeof(Double)));
 list.Add(new DataColumn("date", typeof(DateTime)));
 var table = new DataTable("statement");
 table.Columns.AddRange(list.ToArray());
 var row = table.NewRow();
 row["amount"] = 1.2d;
 row["date"] = DateTime.Now.Date;
 table.Rows.Add(row);
 return table;
}
private static void WriteData()
{
 string strConnection = "Server=(local);Database=ScratchDb;Trusted_Connection=True;";
 using (var bulk = new SqlBulkCopy(strConnection, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
 {
 bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("amount", "amount"));
 bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("date", "date"));
 bulk.BatchSize = 25;
 bulk.DestinationTableName = "statement";
 bulk.WriteToServer(GetTable());
 }
}

As already stated by Amal, you need the column mappings because of the Identity column.

• rusty: Shouldn't that be "SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls"?

Answer 2

The SQL Date type is different to the SQL DateTime type. I think the date column in your table needs to be of type DateTime, based on the way you are using it.

SQL Date Type
SQL DateTime type

Update:

I think Marc's answer should work, but you probably need to specify the SqlBulkCopyColumnMappings from your source DataTable to the destination, otherwise it might be getting the mapping wrong because the structure of your input table does not match the output table exactly ie order of date and row columns swapped.

eg

var amount = new SqlBulkCopyColumnMapping("amount", "amount");
var date = new SqlBulkCopyColumnMapping("date", "date");
bulk.ColumnMappings.Add(amount);
bulk.ColumnMappings.Add(date);

• abatishchev: Yea, I understand that they are different. But how can I use exactly DbType.Date if only DbType.DateTime / SqlDateTime / DateTime is everywhere?!
• marc_s: You could use the more widely used "DATETIME" in the temporary table you use for bulk loading, and then use actual "DATE" in the "real" data table. You can easily assign a DateTime to a Date in a T-SQL statement.

Answer 3

SqlDateTime represents the original datetime type. Have you tried just using the DateTime .NET type in the DataTable? I would hope that it can convert this to either the TSQL datetime or date types. Ditto decimal instead of SqlDecimal.

• abatishchev: If I use typeof(Double) than will get error: "The given value of type SqlString from the data source cannot be converted to type decimal of the specified target column". The same errors with typeof(DateTime)! And I cannot use typeof(Decimal) because appropriate column represents money and requires 2 decimal digits..