Prev: Using a 'Union' kills the performance
Next: Mnipulating temp table output in sql
From: Eddie Pazz on 24 Jun 2010 02:11
Hey folks,

I have a table in one server (SQL2K) that, among other columns, has a
[datetime] column: ActionDate. In a linked server (also SQL2K), this same
table exists which needs to be updated nightly with new records from the
original since the last sync. I have the following:

insert into <synctable>
select * from <linkedserver>.<dbase>.<table> a
where a.ActionDate > (select max(ActionDate) from <synctable>)

This seems like it should work, but I thought I run it by gurus here for
some pointers.

I'd also hope to do something similar, but without a linked server. Can DTS
handle something like this?

Thanks in advanced.


From: Iain Sharp on 24 Jun 2010 08:52
On Wed, 23 Jun 2010 23:11:52 -0700, "Eddie Pazz" <drpazz(a)hotmail.com>
wrote:

>Hey folks,
>
>I have a table in one server (SQL2K) that, among other columns, has a
>[datetime] column: ActionDate. In a linked server (also SQL2K), this same
>table exists which needs to be updated nightly with new records from the
>original since the last sync. I have the following:
>
>insert into <synctable>
> select * from <linkedserver>.<dbase>.<table> a
> where a.ActionDate > (select max(ActionDate) from <synctable>)
>
>This seems like it should work, but I thought I run it by gurus here for
>some pointers.
>
>I'd also hope to do something similar, but without a linked server. Can DTS
>handle something like this?
>
>Thanks in advanced.
>

This assumes actiondate is never updated.

If it's just new records you're after then

insert into <synctable>
select * from <linkedserver>.<dbase>.<table> a
where not exists
(select 1 from <synctable> b where a.<primary key> = b.<primary key>)

will find records with new primary keys.

Iain
 | 
Pages: 1
Prev: Using a 'Union' kills the performance
Next: Mnipulating temp table output in sql