How to insert an increment value via SQL

Question

I have a column in my database. How do i insert an incremented number via an insert so it fills it every row?

Hope you will get one of useful solution using [Increment Sequence Number on Sql Insert][1] [1]: stackoverflow.com/questions/15530222/… — Twinkle
– Twinkle, Commented Aug 2, 2013 at 9:17

Nenad Zivkovic · Accepted Answer · 2013-08-02 09:07:41Z

Use Identity column

Suppose you want Column Id to be incremented automatically a row is inderted define it as identity

ID INT IDENTITY (1,1)

First 1 is starting value second 1 is seeding it means increment by which integer

in this case the value will start at 1 and increment by 1 every time u insert a new row.

Please let me know if any further help needed

Giannis Paraskevopoulos · Accepted Answer · 2013-08-02 09:03:35Z

You may go to the designer of the table add a new Column and then go to the properties tab for the column and set

Identity Specification

Identity Increment sets the number that will be added each time you insert a row. If it was 10 then you would have ids like 10, 20, 30.

Identity Seed is an offset you may need to add (which is the first number to appear) If it was 10 then your first Id would be 10.

Your father · Accepted Answer · 2013-08-02 09:05:30Z

Set identity seed for the column you want to increment the value

Alter Table Names Add Id_new Int Identity(1, 1) Go Alter Table Names Drop Column ID Go Exec sp_rename 'Names.Id_new', 'ID', 'Column'

JsonStatham · Accepted Answer · 2013-08-02 09:05:20Z

When creating a new table, set the Data Type field to int and de-select Allow Nulls for that column.

Then, in column Properties, expand Identity Specification and change (Is Identity) to Yes.

By default the Identity Increment should be set to 1.

hima · Accepted Answer · 2013-08-02 09:06:01Z

Make that column identity while creating the table which will auto increment that column for each row. You don't have to do explicit insert.

Community · Accepted Answer · 2017-05-23 12:20:23Z

Create auto increment for that column for example

CREATE TABLE table_name ( id int NOT NULL IDENTITY (1, 1), name varchar(50) NULL ) ON [PRIMARY]

In the above example column id is auto increment. After each insert its value will increment by 1.

If you want to change that increment number later, please check

6 Answers 6