CODEWITHSUNDEEP
sqlsql-server
Michael Miner
Michael Miner

Reputation: 964

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

Upvotes: 1

Views: 6552

Answers (5)

JamieD77
JamieD77

Reputation: 13949

you could always just create a new sequence on the fly an drop it each time after you use it..

CREATE SEQUENCE CountBy1000
    START WITH 1000
    INCREMENT BY 1000 ;

INSERT INTO Table1
VALUES ((select max(id) from table1 as T) + NEXT VALUE FOR CountBy1000, DATA), 
       ((select max(id) from table1 as T) + NEXT VALUE FOR CountBy1000, DATA);

DROP SEQUENCE CountBy1000;

Upvotes: 0

John D
John D

Reputation: 1637

You can get a race condition using max(id) if 2 users are trying to insert at the same time - they could both end up with the same id value. You could try using GUID's instead of integer ID's (uniqueidentifier type). Use NEWID() function which always returns a new unique GUID number. It's a bit of a pain to convert from integer keys to GUID keys, but it's worth it. There is a slight performance hit, however, and they are much harder to read! One nice advantage is that you can import fresh data from production into your test database without having to worry about duplicate keys.

Upvotes: 1

ArturoAP
ArturoAP

Reputation: 432

You can declare the field this way:

MyID INT IDENTITY (0,1000);

This will auto increment each record by 1000.

For example:

CREATE TABLE MyTable
(
    MyID INT IDENTITY(0,1000),
    SField  VARCHAR(128)
);

INSERT INTO MyTable (SField) VALUES ('TEST');
INSERT INTO MyTable (SField) VALUES ('TEST1');
INSERT INTO MyTable (SField) VALUES ('TEST2');


SELECT * FROM MyTable

Will yield the following result:

| MyID | SField |
-----------------
| 0    |   TEST |
| 1000 |  TEST1 |
| 2000 |  TEST2 |

Upvotes: 5

Spade
Spade

Reputation: 2280

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?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can also do this using ROW_NUMBER():

with v(data) as (
      select v.*, row_number() over (order by (select null)) as seqnum
      from (values(data), (data), (data)) v
     )
insert into table1 (id, data)
    select @maxid + seqnum * 1000, data
    from v;

Upvotes: 2

Related Questions