Insert Into..Select Problem

[Steve Bishop]

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:

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Steve Bishop (st****@viper.com) writes:

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.
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp