HOW DO I DISABLE AN IDENTITY COLUMN WHEN USING SQLLOADER?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Microsoft SQL Server
Post Reply
Welton3
Posts: 23
Joined: Thu 04 Sep 2014 15:36

HOW DO I DISABLE AN IDENTITY COLUMN WHEN USING SQLLOADER?

Post by Welton3 » Fri 09 Aug 2019 15:51

We are migrating from Oracle to SQL Server.

For previous projects we have used the OracleLoader to populate tables in Oracle. The OracleLoader has an Options property that allows me to specify things such as KeepPrimaryKeys, DisableTriggers, etc.

The SqlLoader has no such property. Given that the primary key columns of our SQL Server tables are defined as Identity columns, how can I disable the auto-increment feature of the column to allow the original primary key values to be assigned?

I have tried using

Code: Select all

set identity_insert <my_table> off
but it had no effect. After writing the data to the table with the SqlLoader, all rows had new primary key values.

Shalex
Site Admin
Posts: 8829
Joined: Thu 14 Aug 2008 12:44

Re: HOW DO I DISABLE AN IDENTITY COLUMN WHEN USING SQLLOADER?

Post by Shalex » Mon 12 Aug 2019 16:34

Please use Devart.Data.SqlServer.SqlBulkCopy with SqlBulkCopyOptions.KeepIdentity:

Code: Select all

CREATE TABLE [dbo].[forum39152](
	[id] [int] IDENTITY(100,1) PRIMARY KEY,
	[data] [varchar](100) NULL
) ON [PRIMARY]
GO

    var connString = @"server=dbmssqlx64\mssql2016;initial catalog=alexsh;user id=sa;";
    var bulkCopy = new Devart.Data.SqlServer.SqlBulkCopy(connString, SqlBulkCopyOptions.KeepIdentity);
    bulkCopy.DestinationTableName = "forum39152";
    var table = new DataTable();
    table.Columns.Add("id", System.Type.GetType("System.Int32"));
    table.Columns.Add("data", System.Type.GetType("System.String"));
    for (int i = 0; i < 10; i++)
    {
        var row = table.NewRow();
        row["id"] = i;
        row["data"] = "number " + i;
        table.Rows.Add(row);
    }
    bulkCopy.WriteToServer(table);

Post Reply