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]: http://stackoverflow.com/questions/15530222/incrementing-sequence-number-on-sql-insert — Twinkle, Aug 02 '13 at 09:17

score 8 · Accepted Answer · edited Aug 02 '13 at 09:07

8

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

edited Aug 02 '13 at 09:07

Nenad Zivkovic

answered Aug 02 '13 at 09:05

Ashutosh Arya

score 3 · Answer 2 · answered Aug 02 '13 at 09:03

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.

score 2 · Answer 3 · answered Aug 02 '13 at 09:05

2

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'

answered Aug 02 '13 at 09:05

Your father

score 1 · Answer 4 · answered Aug 02 '13 at 09:05

1

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.

answered Aug 02 '13 at 09:05

JsonStatham

score 0 · Answer 5 · answered Aug 02 '13 at 09:06

0

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.

answered Aug 02 '13 at 09:06

hima

score 0 · Answer 6 · edited May 23 '17 at 12:20

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 Answers6