Steve Bishop (steveb@viper.com) writes:
[quoted text, click to view] > I have this stored proc that is to look for changes only between 2
> tables and inserts the changes into one of my temp tables that I later
> delete in my DTS flow.
>
> I am running into a problem I think becasue in my query I am joining on
> fields that may not exist yet in one of the tables.
>
> Is there a way to compare the 2 tables in the query without joining
> them? Here is my current query:
Maybe there is, but for all such questions, it helps a lot if you
post:
o The CREATE TABLE statements for your tables (with the columns they
have
o INSERT statements with sample data.
o The output given the sample.
It goes without saying that it there is no column on which we can
match the tables to each other the comparison becomes somewhat difficult.
You can't compare if you don't know what to compare.
[quoted text, click to view] > CREATE PROCEDURE [DBO].[ChangesOnly] AS
>
> INSERT INTO ResultSet ( CustomerNumber, CustomerName, AddressLine1,
> AddressLine2, AddressLine3,
> City, State, Zipcode, Division )
> SELECT A.CustomerNumber, A.CustomerName, A.AddressLine1,
> A.AddressLine2, A.AddressLine3, A.city, A.State,A.Zipcode, A.Division
> FROM MyClone As A INNER JOIN tCustomers As B ON a.CustomerNumber =
> B.CustomerNumber AND
> A.customerName = B.CustomerName And A.Division = B.Division
> WHERE
> A.AddressLine1 <> B.AddressLine1 OR
> A.AddressLine2 <> B.AddressLine2 OR
> A.AddressLine3 <> B.AddressLine3 OR
> A.city <> B.city OR
> A.State <> B.State OR
> A.Zipcode <> B.Zipcode OR
> A.Division <> B.Division;
> GO
Note that this query only works if:
o All rows are in both tables.
o All columns in the WHERE clause are non-NULL.
Often when you need to compare tables, you may have differnt rows in
the tables, so you should use a FULL OUTER JOIN instead. As a consequence
of this, you should change the SELECT list to:
coalesce(A.CustomerNumber, B.CustomNumber) etc
And the WHERE clauses to:
(A.AddressLine1 <> B.AddressLine1 OR
A.AdressLine1 IS NULL AND B.AdderssLine1 IS NOT NULL OR
A.AdressLine1 IS NOT NULL AND B.AdderssLine1 IS NULL) OR
etc
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
