Using Cursors To Update A Table From A Remote Server

Problem:

Two tables t1 and t2 have the same schema but exist on two different servers. Which is the better technique for updating t2 and why?

/****** Object: Table [dbo].[t1] Script Date: 9/6/2007 9:55:21 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t1]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[t1] (
k [int] IDENTITY (1, 1) NOT NULL ,
a [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
b [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
c [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
x [int] NULL ,
y [int] NULL ,
amt [money] NULL
) ON [PRIMARY]
END

GO

/****** Object: Table [dbo].[t2] Script Date: 9/6/2007 9:55:44 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t2]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[t2] (
k [int] IDENTITY (1, 1) NOT NULL ,
a [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
b [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
c [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
x [int] NULL ,
y [int] NULL ,
amt [money] NULL
) ON [PRIMARY]
END

GO

-- Technique 1:

set identity_insert t2 on

insert into t2 (k,a,b,c,x,y,amt)
select k,a,b,c,x,y,amt from t1
where not exists (select k from t2 where t1.k = t2.k)

set identity_insert t2 off

update t2
set a = t1.a,
b = t1.b,
c = t1.c,
x = t1.x,
y = t1.y,
amt = t1.amt
from t1
where t1.k = t2.k

-- Technique 2:
set identity_insert t2 on

declare t1_cur cursor for
select k,a,b,c,x,y,amt from t1
for read only

open t1_cur

declare @k int
declare @a char(10)
declare @b char(10)
declare @c char(10)
declare @x int
declare @y int
declare @amt money

fetch next from t1_cur into @k,@a,@b,@c,@x,@y,@amt
while(@@FETCH_STATUS = 0)
begin
if exists(select k from t2 where k = @k)
begin
update t2
set a = @a, b = @b, c = @c, x = @x, y = @y, amt = @amt
where (k = @k)
end
else
begin
insert into t2 (k,a,b,c,x,y,amt) values(@k,@a,@b,@c,@x,@y,@amt)
end

fetch next from t1_cur into @k,@a,@b,@c,@x,@y,@amt
end

close t1_cur
deallocate t1_cur

set identity_insert t2 off


Thanks,

Joel K
Database Adminstration/Application Development