SqlBulkCopy Options KeepIdentity

I am trying to move a table and I like to keep the identity value from the source but the Destination Table keeps auto assign unique identifier. The Destination Table has no records. Do I need to remove the Destination Table IDENTITY setting for this to work?

// Create the SqlBulkCopy object using a connection string. 
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
                {


                    bulkCopy.DestinationTableName = "Log";
                    bulkCopy.ColumnMappings.Clear();
                    bulkCopy.ColumnMappings.Add("ID", "ID");
                    bulk
                    // How many Rows you want to insert at a time
                    //bulkCopy.BatchSize = 100000;
                    bulkCopy.BatchSize = 500;
                    // Set the timeout.
                    bulkCopy.BulkCopyTimeout = 0;

                    // Set up the event handler to notify after 4500 rows.
                    bulkCopy.SqlRowsCopied +=
                        new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                    bulkCopy.NotifyAfter = 4500;


                    //(                  2093,000 row(s) affected)
                    //Always stopping at 2093,000
                    try
                    {
                        // INSERT only if row doesn't exist in the destination
                        //bulkCopy.InsertIfNotExists = true;
                        // Write from the source to the destination.
                        bulkCopy.WriteToServer(reader);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        WriteLog("Log_10_11_18.txt", String.Format("Exception = {0}", ex.Message));
                        return false;
                    }

1 answer

  • answered 2018-10-11 21:11 BoCoKeith

    This works for me...

        static void Main(string[] args) {
    
            //
            // Table definition:
            //
            // CREATE TABLE dbo.Test(Id INT IDENTITY(1, 1) NOT NULL, Content VARCHAR(MAX) NULL);
            //
    
            // Create some dummy data.
            var table = new DataTable();
            table.Columns.Add(new DataColumn("Id", typeof(System.Int32)));
            table.Columns.Add(new DataColumn("Content", typeof(System.String)));
            var row = table.NewRow();
            row["Id"] = 12345;
            row["Content"] = "Testing";
            table.Rows.Add(row);
    
            // Bulk copy it in.
            using (var connection = new SqlConnection(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=Sandbox;Integrated Security=SSPI"))
            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, null)) {
                connection.Open();
                bulkCopy.DestinationTableName = "dbo.Test";
                bulkCopy.WriteToServer(table);
            };
    
            //
            // To check results:
            //
            // SELECT * FROM dbo.Test;
            //
    
        }