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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy