How can I auto increment the primary key in SQL Server 2016 merge insert without sequences?

Question

I am writing a query to import data from one table to a new table. I need to insert records that do not exist in the new table, and update records that do exist. I am trying to use a MERGE "upsert" method.

I have some unique problems due to the client's database and application structure. The table I am inserting into has a Unique ID field that increments by 1 for each new row, but the table does not do the auto incrementating; the insert statement needs to pull the highest ID in the target table and add 1 for the new record.

From my research, I can't figure out how to do that with MERGE. I do not database permissions to create a sequence. I have tried a lot of things, but currently my query looks like:

MERGE
    dbo.targetTable as target
USING
    dbo.sourceTable AS source
ON
    target.account_no = source.account_ID

WHEN NOT MATCHED THEN
    INSERT (
        ID,
        FIELD1,
        FIELD2,
        FIELD3
) VALUES (
        (SELECT MAX(ID) + 1 FROM dbo.targetTable),
        'field1',
        'field2',
        'field3'
)

The problem I am then running into with this code is that it appears to only run the select statement for the new ID once. That is, if the highest ID in the target table was 10, it would insert every new record with ID 11. That won't work as I'm getting a Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object error. I've been doing a ton of googling and trying different things but haven't been able to figure this one out. Any help is appreciated, thank you.

EDIT: For clarification, the unique ID column does not auto-populate. If I do not insert a value for the ID column, I get Cannot insert the value NULL into column 'ID', table 'dbo.targetTable'; column does not allow nulls. UPDATE fails.

And again, as I mentioned originally I do not have permissions to create sequences. It just throws an error and says I do not have permission to do that.

I agree that changing the ID column to auto-increment automatically would be perfect, but I do not have the capability to modify the table like that either.

Answer 1

If you don't need the IDs to be consecutive, you can add the last available ID to a ROW_NUMBER() to generate new, non-repeated IDs.

BEGIN TRANSACTION

    DECLARE @NextAvailableID INT = (SELECT ISNULL(MAX(ID), 0) FROM dbo.targetTable WITH (TABLOCKX))

    ;WITH SourceWithNewIDs AS
    (
        SELECT
            S.*,
            NewID = @NextAvailableID + ROW_NUMBER() OVER (ORDER BY S.account_ID)
        FROM
            dbo.sourceTable AS S
    )
    MERGE
        dbo.targetTable as target
    USING
        SourceWithNewIDs AS source
    ON
        target.account_no = source.account_ID
    WHEN NOT MATCHED THEN
        INSERT (
            ID,
            FIELD1,
            FIELD2,
            FIELD3
    ) VALUES (
            NewID,
            'field1',
            'field2',
            'field3'
    )

COMMIT

Keep in mind that this example is missing the proper error handling with rollback and the lock used to retrieve the max ID will block all other operations until commited or rollbacked.

If you need the new rows to have consecutive IDs then you can use this same approach with a regular INSERT (with WHERE NOT EXISTS...) instead of a MERGE (will have to write the UPDATE separately).

Answer 2

This is just a different way without using a Merge. Permissions aren't required for temp tables, so I would use one to hold the account numbers that need to be inserted, with an identity field to help with traversal. A while loop can traverse the identity, inserting the values with respect to the source table's account_no- into the target table. Since the insert is done in a loop, the MAX function should grab the target table's MAX(account_no) correctly on each loop.

DECLARE @tempTable TABLE (pkindex int IDENTITY(1,1) PRIMARY KEY, account_no int)
DECLARE @current int = 1
       ,@endcount int = 0

--account_no's that should be inserted
INSERT INTO @tempTable(account_no)
SELECT account_no 
  FROM sourceTable 
 WHERE account_no NOT IN (SELECT account_no FROM targetTable)

SET @endcount = (SELECT COUNT(*) FROM @tempTable)

--looping condition, should select the MAX(ID) with each subsequent loop
WHILE (@endcount > 0) AND (@current <= @endcount)
BEGIN
INSERT INTO dbo.targetTable(ID, FIELD1, FIELD2, FIELD3)
SELECT (SELECT MAX(T2.ID) + 1 FROM dbo.targetTable T2) AS MAXID
      ,S.field1
      ,S.field2
      ,S.field3
  FROM @tempTable T INNER JOIN sourceTable S ON T.account_no = S.account_no
 WHERE T.pkindex = @current --traversing temp table by its identity

SET @current += 1

END