How to change Column Properties In SQL?

Question

I have many tables; each has a primary key which is an Identity column with a seed of 1.
I have another program which converts data from a previous database (dBase) to sql.
This programs needs Indentity = No.
How can I change Identity and Identity Seed from my code?

Answer 1

There's no need to break the table for the sake of a data import, just do this:

set identity_insert MyTable on

insert into MyTable ... blah blah blah

set identity_insert MyTable off

Answer 2

It sounds like you want to insert values into the IDENTITY column

You can do that using

SET IDENTITY_INSERT TableName ON

INSERT INTO MyTable (IdentityColumn, Column1, Column2) Values (1, 2, 3)

SET IDENTITY_INSERT TableName OFF

Note: you must specify all the column names

To reseed identity (to lets say start at 77) use the following command

dbcc checkident(TableName, RESEED, 77)