Identity field property lost after import tables between databases

I have found an issue on sql server 2005 data importing function:

For example, I have two databases named DB_A and DB_B. If it has a

table named table_a on DB_A with an identity field (say field_a), then

after transfer table_a to DB_B by using the 'Import and Export Wizard'

of MS SQL Server Management Studio, the identity field of table_a on

DB_B is no longer an identity field. i.e. DB_B.table_a.field_a is not

an identity field any more.

This issue is remain the same as transfering tables from sql server 2000 to 2005, or from sql server 2005 to 2005.

Has anyone know how to work around for this problem?

Thanks in advance.

Athens Yan

P.S.: I have using sql server 2005 with SP1 applied (9.0.2047).

Nobody know that? Or I am the only person to deal with this problem? I just can't believe it!|||

You are not alone. I tried to import tables from a SQL 2005 database into another SQL 2005 database (same instance) and have encountered the problem as well. I noticed that the default setting in the SQL DTSWizard has Identity_Insert not checked. However, checked or not checked, the wizard does not retains the value of identity column.

Have you found a solution?

|||Buddy, it is a dead end for using tools of Microsoft SQL Server 2005 to fix this issue. No any workable way is found. I write a short program to get the job done. The brief of my method is as follows:

Let the source database called DB-A, and the target database is DB-B.

1. Rebuild the database structure of DB-A to DB-B by using 'sysobjects' and 'sp_columns'.

2. Set identity insertion mode on.

3. Read data from all tables on DB-A one by one, and then insert into the corresponding tables on DB-B.

The way is stupid, but work for me. May be it work for you also.

Good luck.
Athens Yan|||

Thanks for the reply.

I started to take the same approach you out lined...not ideal...but as you said, it works.

Thanks again.