MSSQL DB Import issue

[Bastien]

Hi guys,

I have a database that was imported from MySQL to MSSQL via a DTS package. There is an error in that any auto increment (Identity) field is having issues because while the IDENTITY field is yes, the seed value is 0.

Is there an SQL I could run against the db that will allow me to update all tables that have the seed value as 0 to set it to 1?

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[gmmastros]

There is a T-SQL command you can use to get and set the identity value.

To get the current identity value:

DBCC CHECKIDENT(YouTableNameHere)

To set the identity value:

DBCC CHECKIDENT(YouTableNameHere,reseed,1)

Did you also import the data or are your tables empty?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This is important! If your tables already have data, to not reseed it to 1. This will likely (hopefully) cause errors when you insert new rows in to the table.

For example:

-- Create a table with seed = 0, and set primary key
Create Table TestIdentityReseed(id int Primary Key identity(0,1))

-- The next statement returns NULL because there are no rows in the table
dbcc checkident(TestIdentityReseed)

-- Set the seed value to 1
dbcc checkident(TestIdentityReseed, reseed, 1)

-- Insert 4 rows.  Id's become 1, 2, 3, 4
Insert Into TestIdentityReseed Default Values
Insert Into TestIdentityReseed Default Values
Insert Into TestIdentityReseed Default Values
Insert Into TestIdentityReseed Default Values

-- Reset the seed value to 1
dbcc checkident(TestIdentityReseed, reseed, 1)

-- Insert another row
-- The following give a primary key constraint error 
-- because there is already a row with the id = 1
Insert Into TestIdentityReseed Default Values

-- Clean up
Drop Table TestIdentityReseed

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bastien]

George,

(un)fortuantely, the current 0 seed value is blocking most attempts to add more than one row to the table. So I should be able to blanket run that sql to load each table with the correct seed

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[Bastien]

Is this version dependant?

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[gmmastros]

Is this version dependant?

No. All versions and editions of SQL Server have this function and it behaves the same on all of them.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom