When I insert more than 80000 records into an Azure SQL Database table using the below code:
IEnumerable<SqlBulkCopyColumnMapping> columnMapping; db.Database.ExecuteSqlCommand("truncate table dbo.Site"); columnMapping = openXmlParse.GetSiteServiceColumnMappings(); bulkCopy.BatchSize = 2000; bulkCopy.DestinationTableName = "dbo.Site"; bulkCopy.WriteTableToServer(dt, SqlBulkCopyOptions.Default, columnMapping); db.sp_TrimTableColumns("Site");
In local DB it works fine but an exception is thrown when the code is run against Azure SQL Database.
Explicitly set the command timeout to larger value based on how long it takes. .Net default value is 30 seconds and may not be sufficient for large inserts. The command run time varies on the service objective chosen too.