Hi:

I have two diferents hosts with sql server 2000: server1, server2
with two diferents bbdd database1, database2
with two diferents schemas owner1,owner2
with the same set of tables table1,table2,table3,...
these tables have fk a pk with identity columns
I need pass only all rows from
server1.database1.owner1.table1
to
server2.database2.owner2.table1
but
I need preserve pk values and i need advance
identity values y dest tables

I try use dts services but i don`t good results

How I can migrate all rows between owner1 and owner2.


Thanks.

RaulGZ.

"raulgz" <ragaza@ozu.es> wrote in message
news:9b551742.0404212321.1f3f8157@posting.google.c om...
> Hi:
>
> I have two diferents hosts with sql server 2000: server1, server2
> with two diferents bbdd database1, database2
> with two diferents schemas owner1,owner2
> with the same set of tables table1,table2,table3,...
> these tables have fk a pk with identity columns
> I need pass only all rows from
> server1.database1.owner1.table1
> to
> server2.database2.owner2.table1
> but
> I need preserve pk values and i need advance
> identity values y dest tables
>
> I try use dts services but i don`t good results
>
> How I can migrate all rows between owner1 and owner2.
>
>
> Thanks.
>
> RaulGZ.

It would be good if you could give the CREATE TABLE statements for each
table, and some sample data. As a guess, this may be what you want:

use database2
go

set identity_insert owner2.table1 on
go

insert into owner2.table1
(idcol, col1, col2, col3, ...)
select idcol, col1, col2, col3, ...
from server1.database1.owner1.table1 s1
where not exists (select * from owner2.table1 s2
where s1.idcol = s2.idcol)
go

set identity_insert owner2.table1 off
go

dbcc checkident('owner2.table1', reseed)
go

Note that this only inserts row where the PK value from server1 does not
exist in the table on server2 - this may or may not be what you want. If
not, then you will need to explain what the logic is for handling rows where
the PK value already exists.

Simon

Hi,



Try dbMaestro. It's a product that allows comparison, migration and
archiving of database schema and data.

You can find it here:

http://www.extreme.co.il





"Simon Hayes" <sql@hayes.ch> wrote in message news:<4087a2f1$1_1@news.bluewin.ch>...
> "raulgz" <ragaza@ozu.es> wrote in message
> news:9b551742.0404212321.1f3f8157@posting.google.c om...
> > Hi:
> >
> > I have two diferents hosts with sql server 2000: server1, server2
> > with two diferents bbdd database1, database2
> > with two diferents schemas owner1,owner2
> > with the same set of tables table1,table2,table3,...
> > these tables have fk a pk with identity columns
> > I need pass only all rows from
> > server1.database1.owner1.table1
> > to
> > server2.database2.owner2.table1
> > but
> > I need preserve pk values and i need advance
> > identity values y dest tables
> >
> > I try use dts services but i don`t good results
> >
> > How I can migrate all rows between owner1 and owner2.
> >
> >
> > Thanks.
> >
> > RaulGZ.
>
> It would be good if you could give the CREATE TABLE statements for each
> table, and some sample data. As a guess, this may be what you want:
>
> use database2
> go
>
> set identity_insert owner2.table1 on
> go
>
> insert into owner2.table1
> (idcol, col1, col2, col3, ...)
> select idcol, col1, col2, col3, ...
> from server1.database1.owner1.table1 s1
> where not exists (select * from owner2.table1 s2
> where s1.idcol = s2.idcol)
> go
>
> set identity_insert owner2.table1 off
> go
>
> dbcc checkident('owner2.table1', reseed)
> go
>
> Note that this only inserts row where the PK value from server1 does not
> exist in the table on server2 - this may or may not be what you want. If
> not, then you will need to explain what the logic is for handling rows where
> the PK value already exists.
>
> Simon