How to insert and update from remote linked server
UPDATED : 2008-10-22 BY CHRIS MORTON
-------------------
Unfortunately at the time I published this article the data was truncated when I submitted it. Unfortunately that stuff is long gone.
Basically it worked like this:
I had a table that mapped the remote sql server (linked server) to the district id that it was associated with.
Then I had a bunch of stored procedures, one for each table that needed an import or update, in all of these stored procedures I had a districtid input parameter, which was resolved to the linked server name it belonged to by a user defined function.
From there the dynamic sql was constructed for example:
If district id was 1 then
Insert into localtable (columnlist plus the districtid) select (exact same column list plus the districtid) from [(linkedserverremotetable(fully qualified name))] where [(remotecriteria)] not in (sub query localtable).
The update works similarly.
Now I had something like 200 stored procedures doing this.
I wrapped them all up in a stored procedure that used a cursor to go through all the districtid's in the linkedsever table and execute them in order.
When I tested it on a local environment it worked very well and it to approximately 2 minutes to import about 2 million records.
This was my first piece of 'advanced' sql.
If I had to do it again I would change some things slightly, and probably use other technology.
However this logic did work, and if the technology is not available (i.e. you don't have enterprise licenses or have older technology)
It is a real shame that my article was truncated. It was eleven word pages long, originally. I have lost it long ago.
Thanks
Chris
-----------------------------
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
--Insert Script
USE [DIMSCONSOLIDATEDData]
GO
/****** Object: StoredProcedure [dbo].[IMPORT_Insert_Proj_ProjDetail] Script Date: 07/31/2007 10:35:22 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[IMPORT_Insert_Proj_ProjDetail] --14
@DistrictID INT
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @TestResult VARCHAR(1)
EXEC @TestResult = dbo.General_TestLinkedServer @DistrictID
IF @TestResult = '1'
/*begin
select * from imalpha.dimsama.dbo.proj_projdetail
end
else
begin
print ('test failed')
*/
BEGIN
BEGIN TRY
--begin Tran
DECLARE @CommandString VARCHAR(1000)
SET @commandstring = 'insert into hr_department (departmentid,department,enabled,districtid)
select departmentid,department,enabled,' + CONVERT(VARCHAR(2), @DistrictID)
+ ' as DistrictID
from ' + dbo.General_FXN_Alias(@DistrictID, 'hr_department')
+ ' where departmentid not in
(select departmentid from hr_department where districtid = '
+ CONVERT(VARCHAR(2), @DistrictID) + ')'
EXEC ( @commandString
)
SET @commandstring = 'insert into proj_projtype (projtypeid,projtype,enabled,districtid)
select projtypeid,projtype,enabled,' + CONVERT(VARCHAR(2), @DistrictID)
+ ' as DistrictID
from ' + dbo.General_FXN_Alias(@DistrictID, 'proj_projtype')
+ ' where projtypeid not in
(select projtypeid from proj_projtype where districtid = '
+ CONVERT(VARCHAR(2), @DistrictID) + ')'
EXEC ( @commandString
)
SET @commandstring = 'insert into proj_projdistinction (projdistinctionid,projdistinction,enabled,districtid)
select projdistinctionid,projdistinction,enabled, '
+ CONVERT(VARCHAR(2), @DistrictID) + ' as DistrictID
from ' + dbo.General_FXN_Alias(@DistrictID, 'proj_projdistinction')
+ ' where projdistinctionid not in
(select projdistinctionid from proj_projdistinction where districtid = '
+ CONVERT(VARCHAR(2), @DistrictID) + ')'
EXEC ( @commandString
)
SET @commandstring = 'insert into proj_projdetail (projcode,projname,estbudget,projdistinctionid,projtypeid,departmentid,fundernumber,lmproject,districtid)
select projcode,projname,estbudget,projdistinctionid,projtypeid,departmentid,fundernumber,lmproject,'
+ CONVERT(VARCHAR(2), @DistrictID) + ' as DistrictID
from ' + dbo.General_FXN_Alias(@DistrictID, 'proj_projdetail')
+ ' where projcode not in
(select projcode from proj_projdetail where districtid = '
+ CONVERT(VARCHAR(2), @DistrictID) + ')'
EXEC ( @commandString
)
SET @commandstring = 'insert into proj_projstatus (projcode,districtid,statusID)
select projcode, ' + CONVERT(VARCHAR(2), @DistrictID)
+ ' as DistrictID ,statusID
from ' + dbo.General_FXN_Alias(@DistrictID, 'proj_projstatus')
+ ' where statusID not in
(select statusID from proj_projstatus where districtid = '
+ CONVERT(VARCHAR(2), @DistrictID) + ')'
EXEC ( @commandString
)
--commit tran
END TRY
BEGIN CATCH
INSERT INTO General_Error_Log
(
ErrorMessage,
[DateTime],
Alias,
Sproc,
ErrorCode
)
VALUES (
'An error occured in the inserting data from '
+ ISNULL(dbo.General_FXN_Alias(@DistrictID, ''),
'unknown server') + ' at '
+ CONVERT(VARCHAR, GETDATE()) + ' on '
+ @@servername
+ '. The error code returned was '
+ ISNULL(CONVERT(VARCHAR, @@Error),
'server does not exist in catalogue')
+ '.',
GETDATE(),
ISNULL(dbo.General_FXN_Alias(@DistrictID, ''),
'unknown server'),
'IMPORT_Insert_Proj_ProjDetail',
@@Error
)
END CATCH
END
END
--Update Script
USE [DIMSCONSOLIDATEDData]
GO
/****** Object: StoredProcedure [dbo].[IMPORT_Insert_Proj_ProjDetail] Script Date: 07/31/2007 10:35:22 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
--select * from general_error_log
--select * from proj_projdetail where districtid = 9
ALTER PROCEDURE [dbo].[IMPORT_Update_Proj_ProjDetail] --14
@DistrictID INT
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @TestResult VARCHAR(1)
EXEC @TestResult = dbo.General_TestLinkedServer @DistrictID
IF @TestResult = '1'
BEGIN
BEGIN TRY
--begin Tran
DECLARE @CommandString VARCHAR(1200)--, @districtid int
--set @districtid = 9
SET @commandstring = 'update hr_department set department = a.department,[enabled] = a.[enabled]
from ' + dbo.General_FXN_Alias(@DistrictID, 'hr_department')
+ ' a where exists (SELECT b.DepartmentID,b.sDate FROM
(SELECT d.departmentid, STATS_DATE(i.id, i.indid) as sdate
FROM ' + dbo.General_FXN_Alias(@DistrictID, 'sysobjects') + ' o,
' + dbo.General_FXN_Alias(@DistrictID, 'sysindexes') + ' i,
' + dbo.General_FXN_Alias(@DistrictID, 'hr_department')
+ ' d
WHERE o.name = ''hr_department'' AND o.id = i.id and i.name = ''PK_HR_Department'') a JOIN
(SELECT d.departmentid, STATS_DATE(i.id, i.indid) as sdate
FROM sysobjects o, sysindexes i, hr_department d
WHERE o.name = ''hr_department'' AND o.id = i.id and i.name = ''PK_HR_Department''
and d.districtid = ' + CONVERT(VARCHAR, @DistrictID)
+ ' ) b ON a.DepartmentID=b.DepartmentID
where b.sDate < convert(datetime,isnull(a.sdate,'''')))'
EXEC ( @commandString
)
SET @commandstring = 'update proj_projtype set projtype = a.projtype, [enabled] = a.[enabled]
from ' + dbo.General_FXN_Alias(@DistrictID, 'proj_projtype')
+ ' a where exists (SELECT b.ProjTypeID,b.sDate FROM
(SELECT d.ProjTypeID, STATS_DATE(i.id, i.indid) as sdate
FROM ' + dbo.General_FXN_Alias(@DistrictID, 'sysobjects') + ' o,
' + dbo.General_FXN_Alias(@DistrictID, 'sysindexes') + ' i,
' + dbo.General_FXN_Alias(@DistrictID, 'proj_projtype')
+ ' d
WHERE o.name = ''proj_projtype'' AND o.id = i.id and i.name = ''PK_proj_projtype'') a JOIN
(SELECT d.ProjTypeID, STATS_DATE(i.id, i.indid) as sdate
FROM sysobjects o, sysindexes i, proj_projtype d
WHERE o.name = ''proj_projtype'' AND o.id = i.id and i.name = ''PK_proj_projtype''
and d.districtid = ' + CONVERT(VARCHAR, @DistrictID)
+ ') b ON a.ProjTypeID=b.ProjTypeID
where b.sDate < convert(datetime,isnull(a.sdate,'''')))'
EXEC ( @commandString
)
SET @commandstring = 'update proj_projdistinction set projdistinction = a.projdistinction ,[enabled] = a.[enabled]
from ' + dbo.General_FXN_Alias(@DistrictID, 'proj_projdistinction')
+ ' a where exists (SELECT b.ProjDistinctionID,b.sDate FROM
(SELECT d.ProjDistinctionID, STATS_DATE(i.id, i.indid) as sdate
FROM ' + dbo.General_FXN_Alias(@DistrictID, 'sysobjects') + ' o,
' + dbo.General_FXN_Alias(@DistrictID, 'sysindexes') + ' i,
' + dbo.General_FXN_Alias(@DistrictID, 'proj_projdistinction')
+ ' d
WHERE o.name = ''proj_projdistinction'' AND o.id = i.id and i.name = ''PK_proj_projdistinction'') a JOIN
(SELECT d.ProjDistinctionID, STATS_DATE(i.id, i.indid) as sdate
FROM sysobjects o, sysindexes i, proj_projdistinction d
WHERE o.name = ''proj_projdistinction'' AND o.id = i.id and i.name = ''PK_proj_projdistinction''
and d.districtid = ' + CONVERT(VARCHAR, @DistrictID)
+ ') b ON a.ProjDistinctionID=b.ProjDistinctionID
where b.sDate < convert(datetime,isnull(a.sdate,'''')))'
EXEC ( @commandString
)
SET @commandstring = 'update proj_projdetail set projname = a.projname ,estbudget = a.estbudget, projdistinctionid = a.projdistinctionid , projtypeid = a.projtypeid , departmentid = a.departmentid ,fundernumber = a.fundernumber, lmproject = a.lmproject
from ' + dbo.General_FXN_Alias(@DistrictID, 'proj_projdetail')
+ ' a where exists (SELECT b.ProjCode,b.sDate FROM
(SELECT d.ProjCode, STATS_DATE(i.id, i.indid) as sdate
FROM ' + dbo.General_FXN_Alias(@DistrictID, 'sysobjects') + ' o,
' + dbo.General_FXN_Alias(@DistrictID, 'sysindexes') + ' i,
' + dbo.General_FXN_Alias(@DistrictID, 'proj_projdetail')
+ ' d
WHERE o.name = ''proj_projdetail'' AND o.id = i.id and i.name = ''PK_proj_projdetail'') a JOIN
(SELECT d.ProjCode, STATS_DATE(i.id, i.indid) as sdate
FROM sysobjects o, sysindexes i, proj_projdetail d
WHERE o.name = ''proj_projdetail'' AND o.id = i.id and i.name = ''PK_proj_projdetail''
and d.districtid = ' + CONVERT(VARCHAR, @DistrictID)
+ ') b ON a.ProjCode=b.ProjCode
where b.sDate < convert(datetime,isnull(a.sdate,'''')))'
EXEC ( @commandString
)
SET @commandstring = 'update proj_projstatus set statusID = a.statusid
from ' + dbo.General_FXN_Alias(@DistrictID, 'proj_projstatus')
+ ' a where exists (SELECT b.statusID,b.sDate FROM
(SELECT d.statusid, STATS_DATE(i.id, i.indid) as sdate
FROM ' + dbo.General_FXN_Alias(@DistrictID, 'sysobjects') + ' o,
' + dbo.General_FXN_Alias(@DistrictID, 'sysindexes') + ' i,
' + dbo.General_FXN_Alias(@DistrictID, 'proj_projstatus')
+ ' d
WHERE o.name = ''proj_projstatus'' AND o.id = i.id and i.name = ''PK_proj_projstatus'') a JOIN
(SELECT d.statusid, STATS_DATE(i.id, i.indid) as sdate
FROM sysobjects o, sysindexes i, proj_projstatus d
WHERE o.name = ''proj_projstatus'' AND o.id = i.id and i.name = ''PK_proj_projstatus''
and d.districtid = ' + CONVERT(VARCHAR, @DistrictID)
+ ') b ON a.statusid=b.statusid
where b.sDate < convert(datetime,isnull(a.sdate,'''')))'
EXEC ( @commandString
)
--commit tran
END TRY
BEGIN CATCH
INSERT INTO General_Error_Log
(
ErrorMessage,
[DateTime],
Alias,
Sproc,
ErrorCode
)
VALUES (
'An error occured in the inserting data from '
+ ISNULL(dbo.General_FXN_Alias(@DistrictID, ''),
'unknown server') + ' at '
+ CONVERT(VARCHAR, GETDATE()) + ' on '
+ @@servername
+ '. The error code returned was '
+ ISNULL(CONVERT(VARCHAR, @@Error),
'server does not exist in catalogue')
+ '.',
GETDATE(),
ISNULL(dbo.General_FXN_Alias(@DistrictID, ''),
'unknown server'),
'IMPORT_Update_Proj_ProjDetail',
@@Error
)
END CATCH
END
END 
