"raulgz" <ra****@ozu.es> wrote in message
news:9b**************************@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