SQL Server - how to manually increment a PK in multiple-row INSERT transaction

Question

I am working in SQL Server. I have a table that has a PK int column. This column does not have auto-increment enabled, and I am not allowed to change the schema. I need to insert lots of rows (perhaps thousands) into this table manually. None of the data inserted will come from any existing table. However, I need to ensure that the PK column gets incremented by +1 for each new row. My current script is like the following:

BEGIN TRAN
    INSERT INTO DB1.dbo.table1
    (PK_col, col1)
    VALUES
    (10, 'a')
    ,(11, 'something')
    ,(12, 'more text')
    ;

where I already know via a pre-query (SELECT MAX(PK_col) + 1) that PK_col is currently at 9.

My problem is ensuring that the PK column gets incremented by +1 for each new row. Because there could be thousands of rows to insert, I want to reduce the possibility of skipping values or a PK constraint violation being thrown. I know that I can achieve this outside of the DB (via Excel), as long as I validate the PK values prior to running the SQL script. However, I would like to create a solution that handles the auto-increment within the TRAN statement itself. Is this possible (without running into a race condition)? If so, how?

Answer 1

The following should do what you want:

INSERT INTO DB1.dbo.table1(PK_col, col1)
    SELECT COALESCE(l.max_pk_col, 0) + row_number() over (order by (select null)) as PK_col,
           col1
    FROM (VALUES ('a'), ('something'), ('more text')) v(col1) CROSS JOIN
         (SELECT MAX(pk_col) as max_pk_col FROM DB1.dbo.table1) l;

You need to be careful with this arrangement. Locking the entire table for the duration of the INSERT is probably a good idea -- if anything else could be updating the table.