Michael Miner Michael Miner - 6 months ago 15
SQL Question

Increment Variable in SQL

I have a table which does not have any auto increments. I have to insert data into this table, each time incrementing the ID by 1000.

So I have

SELECT @maxId = Max(ID)
INSERT INTO TABLE1
(ID, DATA)
VALUES
(@maxId + 1000, DATA),
(@maxId + 2000, DATA),
(@maxId + 3000, DATA)


Instead of explicitly incrementing the ID for each insert, is there a way to have it 'auto-increment'. I can not use one of the
LAST_INSERT_ID()
or something like that, simply because the ID's are created in a weird way

Answer

There is nothing stopping you from doing the following and getting the data inserted correctly.

insert into table1(ID,DATA) 
VALUES ((select max(id) from table1 as T) +1000, DATA), 
       ((select max(id) from table1 as T) +1000, DATA);

Or is it something else that you meant?