Identity column

Question

Post reply

Identity column

NorbertA

Say Hey Kid

Points: 708

May 10, 2004 at 5:50 am

#160870

Hi All,

Can I might increase identity current value on a table ?

For example:

-- Create products table.

CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))

GO

-- Inserting values into products table.

INSERT INTO products (product) VALUES ('screwdriver')

INSERT INTO products (product) VALUES ('hammer')

INSERT INTO products (product) VALUES ('saw')

INSERT INTO products (product) VALUES ('shovel')

GO

select @@identity -- return value is 4

-- and now i want insert values with id >=100 , how can i do it ? any ideas ?

Regards,

Norbert A.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

If you're looking for a way to explicitely insert values into such a column take a llok at SET IDENTITY_INSERT ON/OFF in BOL.

BTW, I would prefer using SCOPE_IDENTITY() rather than @@IDENTITY.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

NorbertA Say Hey Kid Points: 708 More actions · Answer 2

Frank,

thanks for you reply ... but I think you haven't understood my question.

I ask how can I control indentity current value to insert values thats are greater than that identity value.

SET .. ON/OFF dosen't settlement this matter.

Thank you,

Norbert A

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Okay,

CREATE TABLE a_test

(

col1 INT IDENTITY

)

GO

INSERT INTO a_test DEFAULT VALUES

SELECT * FROM a_test

DBCC CHECKIDENT(a_test,RESEED, 99)

INSERT INTO a_test DEFAULT VALUES

SELECT * FROM a_test

DROP TABLE a_test

col1

-----------

1

2

3

4

Überprüfen der Identitätsinformation: aktueller Identitätswert '4', aktueller Spaltenwert '99'.

DBCC-Ausführung abgeschlossen. Falls DBCC Fehlermeldungen ausgegeben hat, wenden Sie sich an den Systemadministrator.

col1

-----------

1

2

3

4

100

Is this it?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Optimist415 Valued Member Points: 66 More actions · Answer 4

You just need to turn off the identity using the syntax mentioned above, then add a higher value, then turn it back on. Sql server will automatically insert at values above the highest identity value in the table.