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.

IDENTITY and IMPORTING DATA FROM EXISTING DATABASE TO A NEW DB

Hi there,
I need to migrate the database which has identity fields defined to a new DB
which will also be having an identity field. For example table A which has
Field A as IDENTITY and this needs to be migrated to Table B which has Field
B as IDENTITY. The data in Table A has been starting from 1 to 1000 and when
I migrate the data I have a constraint that only certain data will be
migrated it may be from 1 to 100 and then 200 to 400 in that case, if Table
B
has idenity field defined then the data I am migrating will be having Field
B
from 1 to 300 which is not correct. So if someone can give suggestions of ho
w
I can accomplish this migration. I also dont want to change the way IDENTITY
Field is defined. I need to have 301 generated for the new row that gets
generated in Table B.
I hope I am making myself clear here. Any help would be greatly appreciated.
Thanks
JohnJohn
1) If the structure of both databases is identical why not use
BACKUP/RESTORE command?
2) Look at SET IDENTITY_INSERT in the BOL
"John @. X" <JohnX@.discussions.microsoft.com> wrote in message
news:8C370CDD-31EC-46AF-9C6F-13223AAAF8EA@.microsoft.com...
> Hi there,
> I need to migrate the database which has identity fields defined to a new
DB
> which will also be having an identity field. For example table A which has
> Field A as IDENTITY and this needs to be migrated to Table B which has
Field
> B as IDENTITY. The data in Table A has been starting from 1 to 1000 and
when
> I migrate the data I have a constraint that only certain data will be
> migrated it may be from 1 to 100 and then 200 to 400 in that case, if
Table B
> has idenity field defined then the data I am migrating will be having
Field B
> from 1 to 300 which is not correct. So if someone can give suggestions of
how
> I can accomplish this migration. I also dont want to change the way
IDENTITY
> Field is defined. I need to have 301 generated for the new row that gets
> generated in Table B.
> I hope I am making myself clear here. Any help would be greatly
appreciated.
> Thanks
> John|||Try setting IDENTITY_INSERT on for that table
See BOL:
http://msdn.microsoft.com/library/d... />
t_7zas.asp
-- Jesse
On Wed, 9 Feb 2005 21:53:01 -0800, "John @. X"
<JohnX@.discussions.microsoft.com> wrote:

>Hi there,
>I need to migrate the database which has identity fields defined to a new D
B
>which will also be having an identity field. For example table A which has
>Field A as IDENTITY and this needs to be migrated to Table B which has Fiel
d
>B as IDENTITY. The data in Table A has been starting from 1 to 1000 and whe
n
>I migrate the data I have a constraint that only certain data will be
>migrated it may be from 1 to 100 and then 200 to 400 in that case, if Table
B
>has idenity field defined then the data I am migrating will be having Field
B
>from 1 to 300 which is not correct. So if someone can give suggestions of h
ow
>I can accomplish this migration. I also dont want to change the way IDENTIT
Y
>Field is defined. I need to have 301 generated for the new row that gets
>generated in Table B.
>I hope I am making myself clear here. Any help would be greatly appreciated
.
>Thanks
>John|||Thank You for the reply.
But the issue is that BACKUP/RESTORE can pull all the data and make the
IDENTITY correct but my requirement is let us say only 100 records of 1000
data is getting migrated and that too is not in a sequence. In that case my
IDENITY field will be getting wacked and the data is not correctly imported
right.
Thanks
John
"Uri Dimant" wrote:

> John
> 1) If the structure of both databases is identical why not use
> BACKUP/RESTORE command?
> 2) Look at SET IDENTITY_INSERT in the BOL
>
>
> "John @. X" <JohnX@.discussions.microsoft.com> wrote in message
> news:8C370CDD-31EC-46AF-9C6F-13223AAAF8EA@.microsoft.com...
> DB
> Field
> when
> Table B
> Field B
> how
> IDENTITY
> appreciated.
>
>|||John
> IDENTITY correct but my requirement is let us say only 100 records of 1000
> data is getting migrated and that too is not in a sequence. In that case
my
> IDENITY field will be getting wacked and the data is not correctly
imported
> right.
After Restortion you have to manage the tables at new database. I think it
would be easier to delete/truncate (whatever) than manage to insert /dts
from another one. But it is my own opinion.
"John @. X" <JohnX@.discussions.microsoft.com> wrote in message
news:F676A343-50B4-49EA-B48D-EAFA0E1615CF@.microsoft.com...
> Thank You for the reply.
> But the issue is that BACKUP/RESTORE can pull all the data and make the
> IDENTITY correct but my requirement is let us say only 100 records of 1000
> data is getting migrated and that too is not in a sequence. In that case
my
> IDENITY field will be getting wacked and the data is not correctly
imported
> right.
> Thanks
> John
> "Uri Dimant" wrote:
>
new
has
and
of
gets