comparing the same three fields from two distinct tables in two distint servers.

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 2000 Forums

Transact-SQL (2000)

comparing the same three fields from two distinct tables in two distint servers.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-08 : 15:58:35

Bac writes "Hi.
I need some help comparing the same three fields from two distinct tables in two distint servers.

The three fields are :
Company - smallint
Order_nbr - int
Line_nbr - int

The servers are linked server.

I need a query to insert that three fields from server1 into server2 only if they still don't exist in server2.

I tried to do :

insert server2.table1 s2 (field1,field2,field3)
select field1,field2,field3
from server1.table1 s1
where (s1.field1 <> s2.field1) or
((s1.field1 = s2.field1) and (s1.field2 <> s2.field2)) or
((s1.field1 = s2.field1) and (s1.field2 = s2.field2) and
( s1.field3 <> s2.field3)).

But this query for some reason multiplies the number or records from the two tables.

Thanks in advance for the Help.

Bac"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-08 : 16:03:26

INSERT server2.database.dbo.table1
SELECT S1.* FROM server1.database.dbo.table1 S1
LEFT JOIN server2.database.dbo.table1 S2
ON (S1.Company=S2.Company AND S1.Order_nbr=S2.Order_nbr AND S1.Line_nbr=S2.Line_nbr)
WHERE S2.Company Is Null AND S2.Order_nbr Is Null AND S2.Line_nbr Is Null

The LEFT JOIN includes all rows from S1, and the WHERE clause ensures that there are no matches for the three columns by testing the S2 columns for NULL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources