473,322 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

migrating rows between diferents schemas

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.
Jul 20 '05 #1
2 2070

"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
Jul 20 '05 #2
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" <sq*@hayes.ch> wrote in message news:<40**********@news.bluewin.ch>...
"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

Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: raulgz | last post by:
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...
2
by: Serdar Yegulalp | last post by:
I'm in the process of migrating a lot of data (millions of rows, 4GB+ of data) from an older SQL Server 7.0 database to a new SQL Server 2000 machine. Time is not of the essence; my main concern...
3
by: Tome73 | last post by:
How can I easily add the rows of DataTable1 to the rows of DataTable2. Both queries are from the same table. I can always use the column names with myRow, but I was wishing for a shortcut. When I...
1
by: Yama | last post by:
Hi, I am really confused. I have created a strong typed dataset for Northwind database Customer table. Now I am loading it with a stream of XML (ADO style) with the following: Customers _cust...
1
by: Pablo Etcheverry | last post by:
I'm migrating a huge application wrote for sql server, this software offers the option for al user to write their own reports. The report editor was developed many years ago, and it uses the...
6
by: Shai Levi | last post by:
Hi, I'm trying to migrate native c++ class to managed c++ class. The native class header definition looks as: class NativeClass { public: typedef void (CbFunc1)(int n,void* p);
1
by: Bob Loveshade | last post by:
I am looking for an example that shows how to select and highlight multiple rows in a DataGrid. My DataGrid is part of a Web User Control which is contained in an ASPX page. I haven't been...
4
by: Collin Peters | last post by:
I have searched the Internet... but haven't found much relating to this. I am wondering on what the best practices are for migrating a developmemnt database to a release database. Here is the...
0
by: c.w.browne | last post by:
Hi, Ive had a bit of a look around for other people with this problem and cant find anything that solves it in my case, so I'm afraid im going to have to bother you all with a post of my own. ...
0
by: Freddie | last post by:
hi, i need a datagrid w/ 2 headers, the datagrid is bound if( ! IsPostBack), Ken Cox kindly shared some code that addes a new header in the ItemDataBound method, so i moved it to Page_Load, and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.