By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,370 Members | 1,590 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,370 IT Pros & Developers. It's quick & easy.

Insert Into..Select Problem

P: n/a
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!
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.