Compare and download - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Compare and download

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2011-03-03 : 08:42:58

I used SP below to compare two tables in Remote and Local SQL server(linked server) and select the deference.
This is a daily job. There are about 300,000 records in Remote. Most time about 1000 deference records will insert into Local table.
How to improve the code? (ID in both tables is primary key)

ALTER PROCEDURE spCompare
AS
BEGIN
SET nocount ON;
BEGIN
CREATE TABLE #a
(
id INT
CONSTRAINT pk_id PRIMARY KEY (id)
)

INSERT INTO #a
(id)
SELECT
id
FROM
[remote].[order_remote].dbo.order a
WHERE NOT EXISTS(SELECT
id
FROM
order_local.dbo.order b
WHERE b.id = a.id)
END

SELECT
x.*
FROM
[remote].[order_remote].dbo.order x
INNER JOIN #a
ON x.id = #a.id
END

chtummala
Starting Member

4 Posts

Posted - 2011-03-03 : 13:09:41

select x.*
from [remote].[order_remote].dbo.order x
left outer join order_local.dbo.order b
on x.ID=b.ID
where b.id is null

chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources