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