How to bulk insert data into a table when the primary ID is set with a sequence

Question

I'm trying to use SqlBulkCopy to insert a number of rows into a table where the Id column is set with a sequence. The sequence and table look something like:

CREATE SEQUENCE [dbo].[MyTableId] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE -2147483648
 MAXVALUE 2147483647
 CACHE  10 
GO

CREATE TABLE [dbo].[MyTable](
    [Id] [int] NOT NULL,
    [SomeColumn] [int] NOT NULL,
    [AnotherColumn] [nvarchar](100) NOT NULL
  CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  )
GO

ALTER TABLE [dbo].[MyTable] ADD
  CONSTRAINT [DF_MyTable_Id]
  DEFAULT (NEXT VALUE FOR [MyTableId]) FOR [Id]
GO

The Code to import the rows looks something like:

var table = new DataTable();

using (var adapter = new SqlDataAdapter($"SELECT TOP 0 * FROM dbo.MyTable", conn))
{
    adapter.Fill(table);
}

foreach (Data d in data)
{
    var row = table.NewRow();

    row["SomeColumn"] = d.someColumnValue;
    row["AnotherColumn"] = d.anotherColumnValue;

    table.Rows.Add(row);
}

using (var bulk = new SqlBulkCopy(conn))
{
    bulk.DestinationTableName = "dbo.MyTable";
    bulk.WriteToServer(table);
}

This fails when writing the data to the server because

System.InvalidOperationException: 'Column 'Id' does not allow DBNull.Value.'

I have tried removing the Id column from the table definition, but this just puts the column ordinals off by one. I've tried setting the Id row to:

table.Columns["Id"].AutoIncrement = true;

but this ignores the sequence, and on repeated runs of the import, restarts the auto-increment value from 0.

How would I instruct the server to generate a new Id value using its sequence? Or is it possible to separately generate a number of values for the sequence prior to creating the new rows in the table?

Have you tried to use `SqlBulkCopyColumnMapping` to configure list of columns? If that doesn't help, another option that comes to mind is to ask the sequence object to generate the required number of IDs in advance (before starting bulk copy) and provide these IDs along with the rest of the row data from the client. Set corresponding options of bulk copy, so that it inserts provided IDs. — Vladimir Baranov, Apr 19 '19 at 08:33
@Arphile Autoincrement isn't enabled on the column - it's using a sequence instead — izb, Apr 19 '19 at 08:38
@VladimirBaranov I'll look at the column mapping option. Any idea how I might go about creating a set of IDs in advance? — izb, Apr 19 '19 at 08:38
I think bulk insert into a table with the `SEQUENCE` should work exactly the same as a bulk insert into a table with `IDENTITY`. See [SqlBulkCopy Insert with Identity Column](https://stackoverflow.com/q/6651809/4116017) — Vladimir Baranov, Apr 19 '19 at 08:46
@VladimirBaranov sp_sequence_get_range might work. bulk insert with an identity column does seem to work, but with a sequence it doesn't seem to matter what options I pass to the bulk copy - it always tries to take the Id value from the source data. — izb, Apr 19 '19 at 08:59
You *must* use explicit column mappings for any non-trivial use of `SqlBulkCopy`, otherwise it will map columns by ordinal (i.e. the physical position of the column in the table), which is almost never helpful. In this case, you need to add mappings for all columns *except* ID, which is easily done (`foreach (Column c in table.Columns) { if ("ID".Equals(c.ColumnName)) continue; bulk.ColumnMappings.Add(c.ColumnName, c.ColumnName); }`). — Jeroen Mostert, Apr 19 '19 at 12:06
Why are you using a sequence instead of an `identity` column? — Gordon Linoff, Apr 19 '19 at 12:29
@JeroenMostert That worked! Added your code in the using block for the SqlBulkCopy part, and it worked perfectly. If you want to write an answer to the Q, I can mark it as accepted :) — izb, Apr 19 '19 at 12:33

score 0 · Answer 1 · answered May 04 '21 at 23:06

What about asking SQL Server for the current sequence value, through a SqlCommand.ExecuteScalar(). Use this SQL statement as the input for the command:

SELECT current_value
FROM sys.sequences
WHERE OBJECT_ID = OBJECT_ID(N'dbo.MyTableId');

Then set column property AutoIncrementSeed to the previous value plus one:

// ... code to get current sequence value
string sqlText = <above sql goes here>;
SqlCommand getSeqValue = new(sqlText, your_connection);
long currentSequenceValue = (long)getSeqValue.ExecuteScalar();

// Construct DataTable structure
// maybe replacing adapter.Fill(table);  
// with 
//adapter.FillSchema(table,SchemaType.Source);

// tell table to start ID on current sequence value + 1 (or AutoIncrementStep)
table.Columns["Id"].AutoIncrement = true;
table.Columns["Id"].AutoIncrementSeed = currentSequenceValue + 1;

// prepare things and bulk insert

Just an idea, haven't tested. :/

How to bulk insert data into a table when the primary ID is set with a sequence

1 Answers1