October 11, 2007 at 1:06 am
Anybody can tell me the right procedure to merge two database of same design & structure,one database is in sql server 2000 & second database is in sql server 2005. the matter is urgent, i will be very thankfull for this help.
October 11, 2007 at 1:40 am
Can you give us more information what do you want to do (to synhronize data in one db comparing with other, or make union of data,...)?
October 11, 2007 at 1:51 am
Please let us know what do you mean by Merge. Are you looking at merging data? Since you specified that the structure is same. You may want to run a DTS package for import/export data. However it would be more relevant to answer if you can post the exact requirement.
Prasad Bhogadi
www.inforaise.com
October 11, 2007 at 1:58 am
actually my some old data is persist in old server ie- in sql server 2000, so i have to migrate & merge that in my new database, the structures of both the database is same. should i do it by DTS Package or By query, please explain me in detail, because i never did this.
Thanks
October 11, 2007 at 2:09 am
You should be really careful in structuring the insert queries as to which one needs to be executed first and what is next based on the dependencies (Relationships) defined on the tables. If you have master and child tables in your database, since you may want to write a stored procedure that inserts the records into master table, fetches the identity or new primary key of master table and runs the inserts on the child table with reference to this parent table. What is the volume of tables and data in your database?
Prasad Bhogadi
www.inforaise.com
October 11, 2007 at 2:27 am
Red gate SQL Data Compare tool is good option for what you need.
Otherwise you can use DTS/SSIS or to create your own script.
Which way to use depend on your time and how often you are going to execute this procedure (once, occasionally or on regular basis).
October 11, 2007 at 2:55 am
If you are going to make a script than you'd write T-SQL commands for every table like this:
-- Server2 is the other server linked on Server1 with proper security
-- table1 on Server1 is going to be synhronized with table1 on Server2
UPDATE (table1)
SET
(column1) = (alias).(column1),
(column2) = (alias).(column2),
...
(columnN) = (alias).(columnN),
FROM (table1)
INNER JOIN (Server2).(db).(owner).(table1) (alias) ON
(table1).(PK_column1) = (alias).(PK_column1)
and (table1).(PK_column2) = (alias).(PK_column2)
...
and (table1).(PK_columnN) = (alias).(PK_columnN)
INSERT INTO (table1) ((column1), (column2), ..., (columnN))
SELECT (alias).(column1), (alias).(column2), ..., (alias).(columnN)
FROM (table1)
RIGHT JOIN (Server2).(db).(owner).(table1) (alias) ON
(table1).(PK_column1) = (alias).(PK_column1)
and (table1).(PK_column2) = (alias).(PK_column2)
...
and (table1).(PK_columnN) = (alias).(PK_columnN)
WHERE (table1).(PK_column1) Is Null
DELETE FROM (table1)
LEFT JOIN (Server2).(db).(owner).(table1) (alias) ON
(table1).(PK_column1) = (alias).(PK_column1)
and (table1).(PK_column2) = (alias).(PK_column2)
...
and (table1).(PK_columnN) = (alias).(PK_columnN)
WHERE (alias).(PK_column1) Is Null
As Prasad said you'd take care about referential integrity so you will execute insert and update statement first going from top tables (parents) to bottom (child). When this is done then you can delete data in reverse order from bottom to top.
October 11, 2007 at 3:46 am
Thanks to Nebojsa Ilic & all friends who guided me, I will try it Trough DTS or with sql script, i think now it will be achieved. Thanks again
Raj
October 11, 2007 at 5:35 am
One other suggestion, I'd use SSIS over DTS especially since you're trying to move data to 2005.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply