0

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

2
  • How many rows are in table2? What is the data type of the ID column in table1? Is the ID column in table1 supposed to be unique?
    – J.D.
    Jul 11, 2023 at 12:40
  • Hi JD - it's a simplified version of the real data, but the select from Table 2 will never be more than 500 rows at any one time in truth - ID is a numeric (16,0)
    – Les
    Jul 11, 2023 at 12:46

1 Answer 1

0

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.

14
  • Thanks JD, I suspected as much. Unfortunately the system is not mine and I'm locked into using the globalID method - not usually a problem as my various stored procedures are only ever updating or inserting a single record in a transaction - but not in this case. :(
    – Les
    Jul 11, 2023 at 13:18
  • @Les sorry, just to confirm, you don't have control over Table1?
    – J.D.
    Jul 11, 2023 at 13:20
  • I do, and could push anything I wanted into the ID column via SQL manager and query (within reason), but it already holds many thousands of records and the proprietary software that references the table directly uses the max value method for inserting new IDs as primary key (non-computed) and I'd rather not risk affecting that. Looking like a non-starter?
    – Les
    Jul 11, 2023 at 13:27
  • @Les "Looking like a non-starter?" - Not yet, still not fully following though. So does the 3rd party software insert into Table1 or Table2?
    – J.D.
    Jul 11, 2023 at 13:35
  • Table 1 for this purpose. Table 2 is just a construct for the example, will actually be a complex select from multiple other tables in the database that I want to use a source. There are multiple columns in each too, but I thought I was making the example easier by giving a barebones example - sorry if this has just confused matters.
    – Les
    Jul 11, 2023 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.