Convert Access AutoNumber to Sql Server Identity

[danmore]

I am trying to convert an Access 2000 database into SQL Server 7. The DTS wizard maps AutoNumber fields into an INT field type. I wonder if there's a way (1) to alter the INT field to an Identity field after it's created? or (2) to force the the DTS to convert AutoNumber Access fields to and Identity?

 

[AshleyRickards]

Answer to question 1 is yes. After the table is imported go to design view and set Identity to Yes and what you want it to increment by. You'll probably want to set this field as the PK.

HTH Ashley L Rickards
SQL DBA

 

[bperry]

>>...You'll probably want to set this field as the PK.

ashley:
That's probably a good idea; nice suggestion.

danmore:
Creating the PK on your Identity column will probably create the index be default as a Clustered Index. But sometimes the PK isn't the best candidate for a clustered key; for example, if you have other potential indexes that are used extensively in range queries, then one of those might make a better clustered key (and leaving the PK index as non-clustered).


just something to keep n mind.

bp

 

[danmore]

Thank you both Ashley and bp. I guess I should have been a little more specific. I have about 100 tables in this database and would like the conversion to be done using a t-sql procedure or use the DTS wizard directly? I have looked in the documentation for the alter table syntax and It seemed like you could not use alter table/alter column to change a field into an Identity. Am I right?

 

[AshleyRickards]

You should be able to use the alter table and change the field type to int. Some examples below.

[your_col_name] [int] IDENTITY (1, 1) NOT NULL


ALTER TABLE [dbo].[yourtablename] WITH NOCHECK ADD
CONSTRAINT [PK_yourindexname] PRIMARY KEY CLUSTERED
(
[yourfieldname]
) ON [PRIMARY]
GO Ashley L Rickards
SQL DBA

 

[danmore]

Ashley, the converted field is an INT type. The question is how do I alter an INT type field into an INT IDENTITY type field? Thanks

 

[AshleyRickards]

In your alter table statement add the IDENTITY (1, 1) in the field type definition as seen in my first example. Then if you want to add the constraint run the second part of the example.

So you start with this:
[your_col_name] [int] NOT NULL

And change to this in teh Alter command:
[your_col_name] [int] IDENTITY (1, 1) NOT NULL



Ashley L Rickards
SQL DBA

 

[danmore]

Ashley,

I must be doing something worng! I do the following:

use InHouse
go
create table test
( t1 int,
t2 char(2)
)
go

alter table InHouse.dan.test alter column t1 int not null
go
alter table InHouse.dan.test alter column t1 int IDENTITY (1,1) not null
go

On the last command I get:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.

 

[bperry]

danmore:
Yes, you can change the column datatype to int. However, although you can add a new Identity column to a table if one does not already exist, unfortunately you CANNOT change an existing column into an Identity column. (ie. Alter Table is not going to change an existing column for you.)

So, can you set the Identity column when the table is created?

bp

 

[AshleyRickards]

This works when you create the table. Although you can go into design view and change the field to identity. It doesn't work using the Alter command. Hmmmmm?

create table test
( t1 int IDENTITY (1,1) not null,
t2 char(2)null
)
go

Ashley L Rickards
SQL DBA

 

[bperry]

I didn't know that would work from EM. I'll have to give it a try. But I'm pretty sure it won't work from Alter. (At least I thought i was pretty sure!)
bp

 

[danmore]

Ashley and bp, thanks guys! It is indeed possible to change a column to an Identity type using the design view so I thought that if you could do it in one way there should be a way to do it in another. But I guess I can't.

When I do the conversion, using the DTS wizard, I import the data into a new database. All the tables imported are created from scratch and the mapping from Access field types into SQL Server field types are done automatically. All my Access AutoNumber fields are mapped to a INT type fields (not Identity). I was hoping that there could be a way to force the DTS to map those fields to a INT IDENTITY type field. Do you have any insights about that? I think I read somewhere that you could import into existing tables with the new fields defined as Identity and there is a way to allow importation of the data from the Access into those IDENTITY fields.

 

[AshleyRickards]

You sure can. When you run the DTS click on Transform. That will bring you to the field mapping. Make sure the table names are the same so it will default to Append and you might need to select Enable Identity Insert option. Ashley L Rickards
SQL DBA

 

[tlbroadbent]

The reason you can change the column to Identity in Enterprise Manager is that EM creates a new table with an identity column, copies the data, deletes the old table and renames the new table to the old name. Start a trace in SQL Profiler to watch what happens when you make the change in EM. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.