How to insert and update from remote linked server

Total article views: 1050 | Views in the last 30 days: 100

 Rate this |   Join the discussion |  Briefcase

The Problem:


i need to join 19 databases on remote linked servers that do not support replication. the net effect must be the same as replication but without GUID's. i have to maintain all relationships and data integrity. the 19 databases are in different regions of the country, each on a different server and therefore can be identified by districtid and alias (the server name). the connections are not necessarily always connected and failures need to be handled. i have read only access to the databases. Only data that has changed must be updated. Only new data must be inserted. Errors must be traceable and failures must be able to be audited.

The Solution:

Set up your linked servers.

Create a table in the 'subscription' database (in this case [DIMSCONSOLIDATEDData]) that maps the district id's against an alais and an installation.
Use this script for example:

USE [DIMSCONSOLIDATEDData]
GO
/****** Object: Table [dbo].[General_District] Script Date: 08/07/2007 08:38:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[General_District](
[DistrictID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[District] [varchar](255) NOT NULL,
[ProvinceID] [int] NOT NULL,
[Enabled] [bit] NOT NULL,
[Alias] [varchar](90) NULL,
CONSTRAINT [PK_District] PRIMARY KEY CLUSTERED
(
[DistrictID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[General_District] WITH CHECK ADD CONSTRAINT [FK_General_District_General_Province] FOREIGN KEY([ProvinceID])
REFERENCES [dbo].[General_Province] ([ProvinceID])
GO
ALTER TABLE [dbo].[General_District] CHECK CONSTRAINT [FK_General_District_General_Province]

Create a districtid column in the 'replication tables' and make it a composite key.
for example:

USE [DIMSCONSOLIDATEDData]
G

Total article views: 1050 | Views in the last 30 days: 100