10

I want a bigint ID column for every row of data that i insert into a table. I want Sql server to generate the numbers. I tried to create a table with a bigint column ID. I want this to be autoincrement with the first value as 1. I tried using [ID] [bigint] AUTO_INCREMENT NOT NULL, in my create table statement, but I got the error - Incorrect syntax near 'AUTO_INCREMENT'. How do I do this ?

Steam
  • 9,368
  • 27
  • 83
  • 122
  • 1
    http://stackoverflow.com/questions/91628/ms-sql-server-2005-how-to-auto-increment-a-field-not-primary-key might cover this for you – synthesizerpatel Nov 07 '13 at 19:47
  • @synthesizerpatel - i was wondering if there is anything I can do in the create statement to get what i want. I am not allowed to use triggers for now. – Steam Nov 07 '13 at 19:49

2 Answers2

18

Can you not just declare it as an IDENTITY column:

[ID] [bigint] IDENTITY(1,1) NOT NULL;

The 1,1 refers to the start index and the amount it is being incremented by.

NOTE: You do not have to provide a value for the ID column when you do an insert. It will automatically choose it. You can modify these values later if required.

EDIT:

Alternatively, you can use a stored procedure to handle all the inserts.

Example:
Stored Procedure will take in variables as you would a normal insert (one variable for every column). The logic within the stored procedure can select the max value currently existing in the table and choose that as its max value. 

DECLARE @yourVariable = SELECT MAX(ID) FROM YourTable

Use @yourVariable as your insert value. You can increment it or change value as necessary.

ImGreg
  • 2,946
  • 16
  • 43
  • 65
  • Its not useful in my case. I already tried that and I get cannot insert null into ID. Obviously because I am inserting nothing there. I want to insert only the rest of the columns and sql server should handle the ID column for me. Makes sense ? – Steam Nov 07 '13 at 19:56
  • Do you need to add NULL values into your inserts because you do not want to have an ID stored with the insert you are doing? Or just because you think you have to insert null into that column? – ImGreg Nov 07 '13 at 20:13
  • ImGreg - I want a column which will only autoincrement itself when a row is added to my table. Its like a counter. Although I dont need it, it will be nice if I could also change the numbering when a row is deleted. – Steam Nov 07 '13 at 20:15
  • I only ask because if you are not interested in inserting NULL's, Identity column (a primary key) does exactly what you want it to. Auto increments with every insert (automatically, you don't apply a value). – ImGreg Nov 07 '13 at 20:22
  • also, look at my answer. – Steam Nov 08 '13 at 00:53
  • IDENTITY(1,1) -- That's great, but what if it's a primary key w/ existing data? – Alkanshel Mar 26 '18 at 22:21
1

I got the answer here - http://www.sqlservercentral.com/Forums/Topic1512425-149-1.aspx

CREATE TABLE Test (
ID BIGINT IDENTITY NOT NULL,
SomeOtherColumn char(1)
)

INSERT INTO Test (SomeOtherColumn)
values ('a')
Steam
  • 9,368
  • 27
  • 83
  • 122
  • Do be aware that you will have gaps due to rollbacks and deletes. That should not be a concern, if it is then you ned to manually figure out the value and taht can be tricky to avoid race conditions. – HLGEM Nov 07 '13 at 22:28
  • @HLGEM - thanks for your points. I am not a DBA or guru like you. I am not there yet. Can you explain what those things mean ? And why have i got two downvotes ? Its not my answer anyway, as the first line clearly mentions. – Steam Nov 07 '13 at 22:33
  • It is probably because the other answer gave the information and it looked as if you posted this to get rep. – HLGEM Nov 08 '13 at 00:47
  • @HLGEM - great. i'll just upvote the other answer and select it. – Steam Nov 08 '13 at 00:53
  • I upvoted. His answer is accurate and provided a link to a similar question asked. The code was only a sample, no need to complicate a basic question. – ImGreg Nov 08 '13 at 03:27