Increment Variable in SQL

Question

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

Create a loop, one insert per iteration, increase @maxid on each iteration. — Ricardo C
– Ricardo C, Commented May 16, 2016 at 20:09
Have you considered possibly using something akin to a tally table, eg a pre-populated table of integers that could be customized in a manner like your requirements, with your INSERTS performed via a join against the tally table....that way you'd at least have a shot to avoid the expense of iterative/loop-based inserts — David W
– David W, Commented May 16, 2016 at 20:10
Unfortunately this is a table at work, so I am not able to change anything — Michael Miner
– Michael Miner, Commented May 16, 2016 at 20:25
Keep in mind that if you do this kind of thing manually you are going to have some challenges with concurrency. — Sean Lange
– Sean Lange, Commented May 16, 2016 at 20:27

ArturoAP · Accepted Answer · 2016-05-16 20:39:29Z

5

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 |

edited May 16, 2016 at 20:39

answered May 16, 2016 at 20:11

ArturoAP

4322 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David W Over a year ago

Seems to me the OP indicated auto-increments weren't an option

ArturoAP Over a year ago

i read it the other way around, he inserts the ID manually but he wants it to be auto-incremented

Michael Miner Over a year ago

The Identity (from what I read) has to be declared when creating the table. Is that correct?

ArturoAP Over a year ago

Yes, you can do it when creating the table, if the table has already been created you can follow this guide to add an identity column stackoverflow.com/questions/8230257/…

Gordon Linoff · Accepted Answer · 2016-05-16 20:22:49Z

2

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;

answered May 16, 2016 at 20:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Spade · Accepted Answer · 2016-05-16 20:29:29Z

1

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?

edited May 16, 2016 at 20:29

answered May 16, 2016 at 20:14

Spade

2,2801 gold badge20 silver badges30 bronze badges

4 Comments

Gordon Linoff Over a year ago

I believe the subqueries are evaluated before the inserts, so the two subqueries will produce the same value.

Spade Over a year ago

I don't think so and I tested without issues, on MySQL

Michael Miner Over a year ago

This does not seem to work on Microsoft SQL Server Mgmt

Spade Over a year ago

@MichaelMiner Does it return an error or does it duplicate values?

John D · Accepted Answer · 2016-05-16 20:46:07Z

1

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.

answered May 16, 2016 at 20:46

John D

1,6271 gold badge12 silver badges10 bronze badges

1 Comment

Sean Lange Over a year ago

As long the GUID is not your clustered index they work fine....but as mentioned they are pain to work with.

JamieD77 · Accepted Answer · 2016-05-16 21:43:02Z

0

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;

answered May 16, 2016 at 21:43

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Collectives™ on Stack Overflow

Increment Variable in SQL

5 Answers 5

4 Comments

Comments

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related