[Python] Bulk insert from python into MSSQL when the table PK uses a sequence

I have a scenario like this:

I have a table in MSSQL with an 'id' column.

I want the value of that column to be unique AND for the range to depend on another column - let's call it 'type'. So for example, if type = 1, I want the id to be between 0-99999 If type = 2, I want the id to be between 100000-199999, etc

I can accomplish all of this using sequences.

I create sequence_a for the first range, sequence_b for the second range, etc

And then I choose the sequence to use based on the type.

However, then, I can only insert one row at a time - I have to get the next value from the sequence, and then insert.

This is bad for performance AND introduces a race condition.

I was hoping I could put conditional logic in the create table statement, like this:

CREATE TABLE test(
[ID] [bigint] NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT
(
CASE
WHEN type = 1 THEN NEXT VALUE FOR sequence_type1
ELSE
NEXT VALUE FOR sequence_type2
END
),
[type] [int] NOT NULL,


But that doesn't appear to be valid.

I've never had to solve this particular problem before and I'm wondering if there's a better way to do this.

Thanks

Continue reading...