具有不同排序規則的SqlBulkCopy

.net sqlbulkcopy sql-server

我需要將數據從一個DB遷移到另一個DB。我選擇使用SqlBulkCopy,但是有問題,因為源數據庫的排序規則與目標不同,所以,我有一個例外:

System.InvalidOperationException: The locale id '1049' of the source column 'Id' and the locale id '1033' of the destination column 'Id' do not match.
   at System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at MigrateToNormalized.DirectMapCommand.Migrate(SqlConnection source, SqlConnection destination, SqlTransaction transaction) in D:\Projects\APS\DTE\MigrateTo
Normalized\MigrateToNormalized\MigrateToNormalized\DirectMapCommand.cs:line 53
   at MigrateToNormalized.Program.Main(String[] args) in D:\Projects\APS\DTE\MigrateToNormalized\MigrateToNormalized\MigrateToNormalized\Program.cs:line 32

任何人都可以告訴我,如何在不直接使用SQL查詢中的COLLATE語句的情況下解決此問題?是否有一些簡單的方法來更改源數據庫中所有列的排序規則?

熱門答案

當我們使用SqlBulkCopy時,有時它會給出錯誤,這是使用SqlBulkCopy時映射列的最佳方法。

我的首要代碼:

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder("Data Source=ServerName;User Id=userid;Password=****;Initial Catalog=Deepak; Pooling=true; Max pool size=200; Min pool size=0");

SqlConnection con = new SqlConnection(cb.ConnectionString);

        SqlCommand cmd = new SqlCommand("select Name,Class,Section,RollNo from Student", con);

        con.Open();

        SqlDataReader rdr = cmd.ExecuteReader();

        SqlBulkCopy sbc = new SqlBulkCopy("Data Source=DestinationServer;User Id=destinationserveruserid;Password=******;Initial Catalog=DeepakTransfer; Pooling=true; Max pool size=200; Min pool size=0");

        sbc.DestinationTableName = "StudentTrans";


        sbc.WriteToServer(rdr);


        sbc.Close();
        rdr.Close();
        con.Close();

代碼給出了錯誤:源列“RollNo”的區域設置ID“0”和目標列“Section”的區域設置ID“1033”不匹配。

列映射後,我的代碼運行成功。

我修改的代碼是:

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder("Data Source=ServerName;User Id=userid;Password=****;Initial Catalog=Deepak;");

        SqlConnection con = new SqlConnection(cb.ConnectionString);

        SqlCommand cmd = new SqlCommand("select Name,Class,Section,RollNo from Student", con);

        con.Open();

        SqlDataReader rdr = cmd.ExecuteReader();


        SqlBulkCopy sbc = new SqlBulkCopy("Data Source=DestinationServer;User Id=destinationserveruserid;Password=******;Initial Catalog=DeepakTransfer;");

        sbc.DestinationTableName = "StudentTrans";

        sbc.ColumnMappings.Add("Name", "Name");
        sbc.ColumnMappings.Add("Class", "Class");
        sbc.ColumnMappings.Add("Section", "Section");
        sbc.ColumnMappings.Add("RollNo", "RollNo");

        sbc.WriteToServer(rdr);
        sbc.Close();
        rdr.Close();
        con.Close();

此代碼正在成功運行。

試試這個並享受吧。



許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow