丢失的数据插入到我的结束sql表后使用sql批量复制
本文关键字:sql 复制 结束 数据 插入 我的 | 更新日期: 2023-09-27 17:54:12
我编写了一个控制台应用程序,它从我的访问表中提取数据,然后使用sql批量复制到临时sql表,然后从临时表将其加载到主sql表中。应用程序按预期工作,但它从访问表中提取26624行,当它在批量复制上执行WriteToServer操作时,它只复制24880行,因此当插入到主sql表时,只插入24880行。大约有1744行丢失了,但我确实得到了一个异常
" SqlDateTime溢出。必须在1753年1月1日12:00:00 AM和12/31/9999 11:59:59 pm。"
我在访问中有列,即开始时间和停止时间,这些列的默认值为data = 12/30/1899,但时间会改变。
用来移动数据的代码:
string sqlConnStrabk = ConfigurationManager.ConnectionStrings["sqlconabk"].ToString();
using (var sqlConn = new SqlConnection(sqlConnStrabk))
{
sqlConn.Open();
using (var cmd = new SqlCommand())
{
cmd.Connection = sqlConn;
cmd.CommandText =
"CREATE TABLE #NetWeightTracking (" +
"[Date] [datetime] NULL," +
"[Unit UPC Base Item] [nvarchar](50) NULL," +
"[Item (Optional)] [nvarchar](50) NULL," +
"[Preset Number] [nvarchar](50) NULL," +
"[Product Group] [nvarchar](255) NULL," +
"[Shift] [nvarchar](255) NULL," +
"[Rotation Code] [nvarchar](255) NULL," +
"[BBD] [nvarchar](255) NULL," +
"[Operator Name] [nvarchar](255) NULL," +
"[Supervisor] [nvarchar](255) NULL," +
"[Production Line] [nvarchar](255) NULL," +
"[Bagger Number] [float] NULL," +
"[Start Time] [datetime] NULL," +
"[Stop Time] [datetime] NULL," +
"[Under Counts] [float] NULL," +
"[Label Wt on Pkg (g)] [float] NULL," +
"[Machine Tare Wt (g)] [float] NULL," +
"[Actual Tare Wt (g)] [float] NULL," +
"[Verify Target Wt (g)] [float] NULL," +
"[Total Count (Proper)] [float] NULL," +
"[Mean Gross (g)] [float] NULL," +
"[Rptd Mean Net (g)] [float] NULL," +
"[Std Dev (g)] [float] NULL," +
"[Max (g)] [float] NULL," +
"[Min (g)] [float] NULL," +
"[TNE (g)] [float] NULL," +
"[Comments] [nvarchar](50) NULL," +
"[Field1] [datetime] NULL," +
"[Field2] [datetime] NULL," +
"[Field3] [nvarchar](255) NULL," +
"[Field4] [nvarchar](255) NULL," +
"[Field5] [nvarchar](255) NULL," +
"[Field6] [nvarchar](255) NULL," +
"[Field7] [nvarchar](255) NULL, " +
"[Row] [int] IDENTITY(1,1) NOT NULL" +
")";
cmd.ExecuteNonQuery();
}
using (SqlTransaction tran = sqlConn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
using (var sbc = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.KeepIdentity, tran))
{
sbc.BatchSize = 10;
sbc.NotifyAfter = 100;
sbc.BulkCopyTimeout = 1000;
sbc.DestinationTableName = "#NetWeightTracking";
Console.WriteLine(DateTime.Now.ToString());
sbc.WriteToServer(du);
Console.WriteLine("After Datatable", DateTime.Now.ToString());
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
Console.WriteLine(DateTime.Now.ToString());
using (var cmd = new SqlCommand())
{
cmd.Connection = sqlConn;
cmd.Transaction = tran;
cmd.CommandText = "SELECT COUNT(*) AS n FROM #NetWeightTracking";
Console.WriteLine("SqlBulkCopy complete. Temp table row count: {0}", cmd.ExecuteScalar());
cmd.CommandText = "TRUNCATE TABLE [dbo].[Net Weight Tracking]";
cmd.ExecuteNonQuery();
Console.WriteLine("Truncated NetWeightTrackingTable");
cmd.CommandText =
"INSERT INTO [dbo].[Net Weight Tracking] (" +
"[Date],[Unit UPC Base Item],[Item (Optional)]," +
"[Preset Number],[Product Group],[Shift],[Rotation Code]," +
"[BBD],[Operator Name],[Supervisor],[Production Line]," +
"[Bagger Number],[Start Time],[Stop Time],[Under Counts]," +
"[Label Wt on Pkg (g)],[Machine Tare Wt (g)]," +
"[Actual Tare Wt (g)],[Verify Target Wt (g)]," +
"[Total Count (Proper)],[Mean Gross (g)],[Rptd Mean Net (g)]," +
"[Std Dev (g)],[Max (g)],[Min (g)],[TNE (g)],[Comments]," +
"[Field1],[Field2],[Field3]) " +
"SELECT Z.[Date]," +
"Z.[Unit UPC Base Item],Z.[Item (Optional)]," +
"Z.[Preset Number],Z.[Product Group],Z.[Shift]," +
"Z.[Rotation Code],Z.[BBD],Z.[Operator Name]," +
"Z.[Supervisor],Z.[Production Line],Z.[Bagger Number]," +
"Z.[Start Time],Z.[Stop Time],Z.[Under Counts]," +
"Z.[Label Wt on Pkg (g)],Z.[Machine Tare Wt (g)]," +
"Z.[Actual Tare Wt (g)],Z.[Verify Target Wt (g)]," +
"Z.[Total Count (Proper)],Z.[Mean Gross (g)]," +
"Z.[Rptd Mean Net (g)],Z.[Std Dev (g)]," +
"Z.[Max (g)],Z.[Min (g)],Z.[TNE (g)],Z.[Comments]," +
"Z.[Field1],Z.[Field2],Z.[Field3] " +
"FROM #NetWeightTracking Z";
Console.WriteLine(DateTime.Now.ToString());
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT COUNT(*) AS m FROM [dbo].[Net Weight Tracking]";
Console.WriteLine("Inserted Records into NetWeightTracking:{0}", cmd.ExecuteScalar());
}
tran.Commit();
}
}
请帮助! !我不知道如何找出为什么1744行没有从我看到的默认12/30/1899之间插入1753年1月1日至12/31/9999从我读到的其他文章中说,如果没有附加日期,它将默认日期设置为1/1/0001,那么我可以理解它超出了预期范围,因此导致溢出。
您需要查看Access中的数据,并找到/修复这些不良列值,或者在导出后在TSQL中处理它们。一个调试选项-将临时表替换为真实表,将datetime数据类型替换为字符串,进行导出,然后查询表以查找错误值,以便您知道要查找哪种数据。之后,您可以将其切换回临时表,但是在导入中,您应该在SELECT语句中处理无效的日期时间格式,可能使用CASE和CONVERT/CAST。