Devart Forums

Posted: **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.

Posted: **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);

Devart Forums

HOW DO I DISABLE AN IDENTITY COLUMN WHEN USING SQLLOADER?

HOW DO I DISABLE AN IDENTITY COLUMN WHEN USING SQLLOADER?

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