丢失的数据插入到我的结束sql表后使用sql批量复制

Question

我编写了一个控制台应用程序，它从我的访问表中提取数据，然后使用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，那么我可以理解它超出了预期范围，因此导致溢出。

Answer 1

您需要查看Access中的数据，并找到/修复这些不良列值，或者在导出后在TSQL中处理它们。一个调试选项-将临时表替换为真实表，将datetime数据类型替换为字符串，进行导出，然后查询表以查找错误值，以便您知道要查找哪种数据。之后，您可以将其切换回临时表，但是在导入中，您应该在SELECT语句中处理无效的日期时间格式，可能使用CASE和CONVERT/CAST。