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, May 16 '16 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, May 16 '16 at 20:10
Unfortunately this is a table at work, so I am not able to change anything — Michael Miner, May 16 '16 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, May 16 '16 at 20:27

ArturoAP · Answer 1 · 2016-05-16T20:39:29.390

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 '16 at 20:39

answered May 16 '16 at 20:11

ArturoAP

432
2
13

Seems to me the OP indicated auto-increments weren't an option – David W May 16 '16 at 20:12
i read it the other way around, he inserts the ID manually but he wants it to be auto-incremented – ArturoAP May 16 '16 at 20:14
2

The Identity (from what I read) has to be declared when creating the table. Is that correct? – Michael Miner May 16 '16 at 20:33
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 http://stackoverflow.com/questions/8230257/sql-server-remove-identity-from-a-column-in-a-table – ArturoAP May 16 '16 at 20:40

score 2 · Answer 2 · answered May 16 '16 at 20:22

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;

Spade · Accepted Answer · 2016-05-16T20:29:29.403

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 '16 at 20:29

answered May 16 '16 at 20:14

Spade

2,220
1
19
29

3

I believe the subqueries are evaluated before the inserts, so the two subqueries will produce the same value. – Gordon Linoff May 16 '16 at 20:23
I don't think so and I tested without issues, on MySQL – Spade May 16 '16 at 20:23
1

This does not seem to work on Microsoft SQL Server Mgmt – Michael Miner May 16 '16 at 20:44
@MichaelMiner Does it return an error or does it duplicate values? – Spade May 16 '16 at 20:47

score 1 · Answer 4 · answered May 16 '16 at 20:46

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.

As long the GUID is not your clustered index they work fine....but as mentioned they are pain to work with. — Sean Lange, May 16 '16 at 21:04

score 0 · Answer 5 · answered May 16 '16 at 21:43

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;

Increment Variable in SQL

5 Answers5