Data Replication solution between tables with different database structures on the two servers !!!

Http://www.uipower.com/bbs/thread-35773-1-1.html

 

/* Data operations between different Server databases */

-- Create a linked server

Exec sp_addmediaserver 'itsv
','
',' Sqloledb
',' Remote Server name or IP address
'

Exec sp_add1_srvlogin 'itsv
', 'False'
', Null,
'User name
',' Password
'
-- Query example

Select
* From itsv. Database Name. DBO. Table Name

-- Import example

Select
* Into table
From itsv. Database Name. DBO. Table Name

-- Delete the linked server when it is no longer in use

Exec sp_dropserver 'itsv
', 'Droplogins
'

-- Connect to remote/LAN data (OpenRowSet/openquery/OpenDataSource)

-- 1. OpenRowSet

-- Query example

Select
* From
OpenRowSet (
'Sqloledb
',' SQL Server Name
';' User Name
';' Password
', Database name. DBO. Table name)

-- Generate a local table

Select
* Into table
From
OpenRowSet (
'Sqlodb ',
'SQL Server Name
';' User Name
';' Password
', Database name. DBO. Table name)

-- Import a local table to a remote table

Insert
OpenRowSet (
'Sqlodb ',
'SQL Server Name
';' User Name
';' Password
', Database name. DBO. Table name)

Select
* From local table

-- Update local table

Update B

Set B. Column A = A. Column

From
OpenRowSet (
'Sqlodb ',
'SQL Server Name
';' User Name
';' Password
', Database name. DBO. Table Name) as
Inner
Join local Table B

On a. column1 = B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server

Exec sp_addmediaserver 'itsv
','
',' Sqloledb
',' Remote Server name or IP address
'

-- Query

Select
*

From
Openquery (itsv, 'select * from database. DBO. Table Name
')

-- Import a local table to a remote table

Insert
Openquery (itsv, 'select * from database. DBO. Table Name
')

Select
* From local table

-- Update local table

Update B

Set B. Column B = A. Column B

From
Openquery (itsv, 'select * from database. DBO. Table Name
') As

Inner
Join local Table B
On a. Column A = B. Column

-- 3. OpenDataSource/OpenRowSet

Select *

From OpenDataSource (
'Sqloledb
', 'Data source = IP/servername; user id = login name Password = Password
'). Test. DBO. roy_ta