实现方法

    • 通过ADO.NET SQLBulkCopy 方式
    • 通过JDBC SQLServerBulkCopy 方式
    2. SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();  
    3. copyOptions.setCheckConstraints(true);
    4. 测试时，请用Microsoft JDBC Drivers 6.0 的sqljdbc41.jar，sqljdbc4.jar及更老版本没有SQLServerBulkCopy 实现。
    6. 例如: 将本地的一个大表通过SQLServerBulkCopy方式导入到RDS的实例中
    9. import java.sql.*;
    10. import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
    11. import com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions;
    13. public class Program {
    14.   public static void main(String[] args)  
    15.   {  
    16.     String sourceConnectionString  = "jdbc:sqlserver://localhost:1433;" +  
    17.             "databaseName=testdb;user=****;password=****";  
    18.     String destConnectionString  = "jdbc:sqlserver://*****.sqlserver.rds.aliyuncs.com:3433;" +  
    19.             "databaseName=testdb;user=****;password=**** ";  
    20.     try  
    21.     {  
    22.       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
    23.       try (Connection sourceConnection =
    24.            DriverManager.getConnection(sourceConnectionString))  
    25.       {  
    26.         try (Statement stmt = sourceConnection.createStatement())  
    27.         {  
    28.           try (ResultSet rsSourceData = stmt.executeQuery(  
    29.                 " SELECT top 1000000 " +
    30.                 "[PersonType],[NameStyle],[Title],[FirstName],[MiddleName],[LastName] ," +
    31.                 "[Suffix],[EmailPromotion],[AdditionalContactInfo]," +
    32.                 "[Demographics],NULL as rowguid,[ModifiedDate] " +
    34.             try (Connection destinationConnection =  DriverManager.getConnection(destConnectionString))  
    35.             {  
    36.               Statement stmt1 = destinationConnection.createStatement();
    38.               long countStart = 0;  
    39.               try (ResultSet rsRowCount = stmt1.executeQuery(  
    40.                       "SELECT COUNT(*) FROM dbo.Person;"))  
    41.               {  
    42.                 rsRowCount.next();  
    43.                 countStart = rsRowCount.getInt(1);  
    44.                 System.out.println("Starting row count = " + countStart);  
    45.               }  
    47.               try (SQLServerBulkCopy bulkCopy =   new SQLServerBulkCopy(destinationConnection))  
    48.               {  
    49.                 SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();  
    50.                 copyOptions.setKeepIdentity(true);  
    51.                 copyOptions.setBatchSize(2000);
    52.                 copyOptions.setBulkCopyTimeout(5000);
    53.                 //this is importance setting
    54.                 copyOptions.setCheckConstraints(true);
    55.                 bulkCopy.setBulkCopyOptions(copyOptions);
    56.                 bulkCopy.setDestinationTableName("dbo.Person");  
    57.                 bulkCopy.addColumnMapping("PersonType", "PersonType");  
    58.                 bulkCopy.addColumnMapping("NameStyle", "NameStyle");  
    59.                 bulkCopy.addColumnMapping("Title", "Title");  
    60.                 bulkCopy.addColumnMapping("FirstName", "FirstName");  
    61.                 bulkCopy.addColumnMapping("MiddleName", "MiddleName");  
    62.                 bulkCopy.addColumnMapping("LastName", "LastName");  
    63.                 bulkCopy.addColumnMapping("Suffix", "Suffix");  
    64.                 bulkCopy.addColumnMapping("EmailPromotion", "EmailPromotion");  
    65.                 bulkCopy.addColumnMapping("AdditionalContactInfo", "AdditionalContactInfo");  
    67.                 bulkCopy.addColumnMapping("rowguid", "rowguid");  
    68.                 try  
    69.                 {  
    70.                   bulkCopy.writeToServer(rsSourceData);  
    71.                 }  
    72.                 catch (Exception e)  
    73.                 {  
    74.                   e.printStackTrace();  
    75.                 }  
    77.                 try (ResultSet rsRowCount = stmt1.executeQuery(  
    78.                       "SELECT COUNT(*) FROM dbo.Person;"))  
    79.                 {  
    80.                   rsRowCount.next();  
    81.                   long countEnd = rsRowCount.getInt(1);  
    82.                   System.out.println("Ending row count = " + countEnd);  
    83.                   System.out.println((countEnd - countStart) + " rows were added.");  
    84.                 }  
    85.               }  
    86.             }  
    87.           }  
    88.         }  
    89.       }  
    90.     }  
    91.     catch (Exception e)  
    92.     {  
    93.         e.printStackTrace();  
    94.     }  
    95.   }  
    96. }
    • 通过BCP方式

    第一步：需要将数据BCP到本地

    第二步：将导出的文件直接导入到RDS的实例中,但需要指定提示：/h “CHECK_CONSTRAINTS”

    1. BCP testdb.dbo.person In "bcp_data" /C /N /q /k /h "CHECK_CONSTRAINTS" /U *** /P *** /b 500 /S  "***.sqlserver.rds.aliyuncs.com,3433"
    • 通过DTS/SSIS方式

    第二种：直接使用SQL Server Business Intelligence Development Stuidio新建 SSIS包：

    • 特别说明

    第一种：

    第二种：

    • 总结