May 16, 2008 at 11:00 pm
I'm not a full-time DBA, so excuse my style of expressing my question.
I have a database which has 2 tables in SQL 2005. Both these tables have similar column names, EXCEPT for new extra columns in FY2007_DATA. I can visually see the difference in columns in Database Diagrams.
My goal is to :- I want to compare FY2007 tbl column names to FY2006 column names and display only those columns as results that do not match.
Tbl 1 :- FY2006_DATA
Tbl 2:- FY2007_DATA
With online reading and help I have managed to get this script to do exactly opposite of what i want. Below is the query
/* This query compares the column names from two tables and displays the ones that have an exact match. It does not care for case-sensitiveness */
Select a.Table_Name, a.Column_Name, (b.Table_Name), (b.column_name)
From [2006-2011].INFORMATION_SCHEMA.Columns AS a
Join [2006-2011].INFORMATION_SCHEMA.Columns AS b on a.Column_Name = b.Column_Name
Where a.TABLE_NAME = 'FY2006_DATA'
And b.TABLE_NAME = 'FY2007_DATA'
AND a.Column_Name IN
(Select Column_Name = LEFT(c.column_name, 20) FROM
[H1B_2006-2011].INFORMATION_SCHEMA.Columns AS c WHERE c.TABLE_NAME = 'FY2007_DATA' )
When I change "AND a.Column_Name IN.." to "AND a.Column_Name NOT IN.." so that the results will (should) display the extra columns in FY2007, in fact I do not see any results, but query executes perfect.
How can I achieve my goal.?
Thank you
May 19, 2008 at 6:26 am
You want to change the INNER join to a FULL OUTER join and then use the WHERE to give you records that do not have a match.
[font="Courier New"]SELECT
*
FROM
Information_Schema.Columns A
FULL OUTER JOIN Information_Schema.Columns B ON A.Column_Name = B.Column_Name
AND B.Table_Name = 'FY2007_DATA'
AND B.Table_Schema = 'dbo'
WHERE
A.Table_Schema = 'dbo'
AND A.Table_Name = 'FY2006_DATA'
AND (A.Column_Name IS NULL OR B.Column_Name IS NULL)[/font]
May 19, 2008 at 4:39 pm
thank you . the script works
April 17, 2009 at 8:58 am
Hi,
Following on from the previous post. I want to take the list of columns that match, so change the outer join to an inner join. Then insert a record into A that exist in B.
The reason to use the first query is the colunms in A dont match B and I dont want to have to type out each column manually.
Any Ideas??
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply