How to import multiple data entries to apsaradb for RDS SQL Server -

Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.

Overview

This topic describes how to batch import data using apsaradb for RDS SQL Server.

Description

Alibaba Cloud reminds you that:

When you perform operations that have risks, such as modifying instances or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.

Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.

If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

Apsaradb for RDS SQL Server allows you to use Bulk Insert statements to import data in batches. However, some restrictions exist because a BUG that may trigger the RDS for SQL Server 2008R2 version of the instance. When you use Bulk Insert enable CheckConstraints.

Use BCP commands

Generate an XML file as follows:

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"

Import data. For 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 "xxx.sqlserver.rds.aliyuncs.com,3333"

SQLBulkCopy through JDBC

You can import data in batches through JDBC SQLBulkCopy as follows:

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

Instructions for more information, see use bulk copy via JDBC driver.

Use ADO.NET SQLBulkCopy

You can use ADO.NET SQLBulkCopy to import multiple data at a time as follows: specify SqlBulkCopy as 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);
        }

Application scope

Apsaradb for RDS SQL Server