MSSQL - Insert Into Select number from global count table and update the global count for each row then inserted

Question

Not sure I've articulated that well in the title, so here goes...

I have three tables

Table1

ID	NewNames
10	some row data
11	some more row data

Table2

ID	OldNames
1	my old data
2	and more data

and

GlobalID (with a single row numeric field)

MaxID
12

I'd like to use a Insert Into Select like below:

INSERT INTO table1 (
ID
,NewNames
) SELECT 'a new id'
,OldNames FROM table2

'a new id' is a numeric value and should equal the (max value + 1) from the GlobalID table.

My problem is I obviously need to keep adjusting the GlobalID table and increase the max for each row as I'm inserting from the Select!

Does that make sense?

Any help appreciated. Using sql server 2016 if that makes any difference. Les

Answer 1

Maintaining your own global max ID table is going to be a pain because it's at risk of incorrect results when there's concurrency (in addition to other reasons). This is why features like an identity column or sequences exist.

You should create Table1 with the ID column as an identity like so:

CREATE TABLE Table1
(
    ID NUMERIC(16,0) IDENTITY(TheStartingValue, 1), -- TheStartingValue should be larger than the max value in Table2
    NewNames NVARCHAR(1000) -- Or whatever size you need
);

Then any inserts from Table2 can be simplified like so:

INSERT INTO table1 (NewNames) 
SELECT OldNames 
FROM table2;

You don't need to maintain your own global ID table this way. The identity will automatically populate (concurrency safe) new values for you in the table as rows are inserted into it.