Importing Autonumber columns from access 1

[SadOldGoth]

Hi Folks,

Probably a dead easy one for you top bods, but how do you import a table with an autonumber field and keep it's status as such, ie, transforming it to an Ident field?

Thanks,

Jes

 

[tlbroadbent]

The default behavior of DTS when importing an Access table is to NOT add the Identity property to the column. You can change this behavior by modifying the SQL Statement used to create the table. After choosing the source and destination tables, click on the button in the Transform column.

1) Select Enable Identity Insert
2) Click on the Edit SQL button
3) Add the identity property to the column in the Create Table statement.
4) Click OK a couple of times and proceed to import the data.

Alternatively, you can add the column without the Identity property and the use Enterprise Manager to add the Identity property to the columns after importing the data.

Another alternmative is to Export the table from Access to SQL Server. When you export from Access, the autonumber column is created as an Identity column on SQL Server table.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SadOldGoth]

Hi Folks,

Once again Terry you've come to the rescue. Marvellous.

Cheers mate,

Jes