ApsaraDB RDS:How do I import multiple data records to an ApsaraDB RDS for SQL Server instance at a time?

Choose a method

SQL Server 2008 R2: CHECK_CONSTRAINTS requirement

On instances running SQL Server 2008 R2, a known bug is triggered when you run a BULK INSERT statement without enabling CHECK constraints. Each method has a different way to enable CHECK constraints — details are included in each method section below.

For background, see Controlling constraint checking by bulk import operations).

Method 1: Use the DMS data import feature

The data import feature of DMS lets you import multiple data files to your RDS for SQL Server instance through the console. Supported file types are SQL scripts, CSV files, and Excel files.

No additional constraint configuration is needed for this method.

Method 2: Use the bcp utility

The bcp utility imports data using an XML format file that describes the table schema. The process has two steps.

Step 1: Generate the XML format file

Run the following command to generate the format file for your table:

bcp [Database name].[Architecture].Table name format nul /c /t"," /x /f "Path\XML-file name.xml" /U Username /P Password /S "Server addresses,Port"

Example:

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"

Step 2: Import data

Include /h "CHECK_CONSTRAINTS" to enable constraint checking. Without this flag on SQL Server 2008 R2, the import fails due to a known bug.

bcp [Database name].[Architecture].Table name in "Path to data files" /f "Path to the generated XML file" /q /k /h "CHECK_CONSTRAINTS" /U Username /P Password /S "Server addresses,Port"

Example:

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 "rm-bp1sc****.sqlserver.rds.aliyuncs.com, 1433"

Method 3: Use JDBC SQLServerBulkCopy

Use the SQLServerBulkCopy class from the Microsoft JDBC driver to bulk-copy data into RDS for SQL Server.

Before running the copy, set setCheckConstraints(true) in SQLServerBulkCopyOptions. Without this setting on SQL Server 2008 R2, the operation triggers a known bug.

SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
copyOptions.setCheckConstraints(true);

For the full API reference, see Using bulk copy with the JDBC driver.aspx).

Method 4: Use ADO.NET SqlBulkCopy

Use the SqlBulkCopy class in the ADO.NET framework to bulk-copy data into RDS for SQL Server.

Pass SqlBulkCopyOptions.CheckConstraints to the constructor. Without this option on SQL Server 2008 R2, the operation triggers a known bug.

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);
}

What's next

• Data migration and synchronization

Application scope

ApsaraDB RDS for SQL Server