云数据库 RDS SQL Server 版如何批量导入数据？

通过BCP命令方式 生成XML格式文件，示例如下。 bcp jacky.dbo.my_bcp_test format nul /c /t"," /x /f "d:\tmp\my_bcp_test.xml"  /U jacky /P xxxx /S "xxx.sqlserver.rds.aliyuncs.com,3333" 导入数据，示例如下。 bcp jacky.dbo.my_bcp_test in "d:\tmp\my_test_data_file.txt" /f "d:\tmp\my_bcp_test.xml"  /q /k /h "CHECK_CONSTRAINTS"  /U jacky /P xxx /S "xxx.sqlserver.rds.aliyuncs.com,3333"

通过JDBC SQLBulkCopy方式 通过JDBC SQLBulkCopy方式批量导入数据的方法如下所示。 SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions(); copyOptions.setCheckConstraints(true); 说明：详情请参见通过JDBC驱动程序使用大容量复制。

通过ADO.NET SQLBulkCopy方式 通过ADO.NET SQLBulkCopy方式批量导入数据的方法如下所示，将SqlBulkCopy指定为SqlBulkCopyOptions.CheckConstraints即可。 static void Main() {

string srcConnString = "Data Source=(local);Integrated Security=true;Initial Catalog=testdb"; string desConnString = "Data Source=****.sqlserver.rds.aliyuncs.com,3433;User ID=;Password=;Initial Catalog=testdb";

SqlConnection srcConnection = new SqlConnection(); SqlConnection desConnection = new SqlConnection();

SqlCommand sqlcmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable();

srcConnection.ConnectionString = srcConnString; desConnection.ConnectionString = desConnString; sqlcmd.Connection = srcConnection;

sqlcmd.CommandText = @"SELECT top 1000000 [PersonType],[NameStyle],[Title],[FirstName],[MiddleName],[LastName],[Suffix],[EmailPromotion] ,[AdditionalContactInfo],[Demographics],NULL as rowguid,[ModifiedDate] FROM [testdb].[dbo].[Person]"; sqlcmd.CommandType = CommandType.Text; sqlcmd.Connection.Open(); da.SelectCommand = sqlcmd; da.Fill(dt);

using (SqlBulkCopy blkcpy = new SqlBulkCopy(desConnString, SqlBulkCopyOptions.CheckConstraints)) //using (SqlBulkCopy blkcpy = new SqlBulkCopy(desConnString, SqlBulkCopyOptions.Default)) { blkcpy.BatchSize = 2000; blkcpy.BulkCopyTimeout = 5000; blkcpy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); blkcpy.NotifyAfter = 2000;

foreach (DataColumn dc in dt.Columns) { blkcpy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); }

try { blkcpy.DestinationTableName = "Person"; blkcpy.WriteToServer(dt); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { sqlcmd.Clone(); srcConnection.Close(); desConnection.Close();

} }

}

private static void OnSqlRowsCopied( object sender, SqlRowsCopiedEventArgs e) { Console.WriteLine("Copied {0} so far...", e.RowsCopied); }