January 6, 2009 at 10:13 am
I am a newbie attempting to use MS SQL 2005 to compare similar tables/date
across multiple databases (all databases on same SQL server). Need to find out
any differences in data between Table A in Database 1 to Table B
in Database 2. Table A and Table B have same data structure and properties.
Table A and Table B both have same columns "m_day" and "mday_num".
See below for data extract.
Would like to find if there are any differences of the 2 data columns
between the 2 tables.
Tried using the following SQL script but it is not working. Even though
there is differences between the 2 tables, the query shows no data.
select q.m_date, q.mday_num
from qvi_app.dbo.mcal q
where not exists
(select i.m_date, i.mday_num
from view_app.dbo.mcal i)
Please help!
Data Extract:
m_day mday_num
2008-01-02 00:00:00.000 1
2008-01-03 00:00:00.000 2
2008-01-04 00:00:00.000 3
2008-01-07 00:00:00.000 4
2008-01-08 00:00:00.000 5
2008-01-09 00:00:00.000 6
2008-01-10 00:00:00.000 7
2008-01-11 00:00:00.000 8
2008-01-14 00:00:00.000 9
2008-01-15 00:00:00.000 10
January 6, 2009 at 10:25 am
You need to add the where condition in the exists clause.
select q.m_date, q.mday_num
from qvi_app.dbo.mcal q
where not exists
(select * from view_app.dbo.mcal i WHERE q.m_date = i.m_date and q.mday_num = i.mday_num)
Alternative :
Select * From TableA
EXCEPT SELECT * FROM TableB
THEN
Select * From TableB
EXCEPT SELECT * FROM TableA
This will give you the data that exists in the first table, but not in the other.
January 6, 2009 at 12:34 pm
When you say you want differences, what exactly do you mean?
Do you just mean rows that exist in one that don't exist in the other? Do you mean rows where the day number is the same, but the date is different, or where the date is the same but the day number is different? Something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 12:52 pm
it's probably easier to use a cursor to iterate through your first table.
declare @c1 varchar(50), @c2 int
declare @rowcount
declare @sel cursor
set @sel = cursor for
select c1,c2 from t1
open @sel
fetch next form @sel into @c1, @c2
begin
select @rowcount = count(*) from t2 where c1 = @c1 and c2 = @c2
if (rowcount = 0)
begin
insert into t3 (info), 'this row not there:' + @c1 + ';' + cast(@c2,nvarchar(50))
end
fetch next form @sel into @c1, @c2
end
"do something like this, and it will get you what you want (data that is missing).
January 6, 2009 at 1:02 pm
foxjazz (1/6/2009)
it's probably easier to use a cursor to iterate through your first table.declare @c1 varchar(50), @c2 int
declare @rowcount
declare @sel cursor
set @sel = cursor for
select c1,c2 from t1
open @sel
fetch next form @sel into @c1, @c2
begin
select @rowcount = count(*) from t2 where c1 = @c1 and c2 = @c2
if (rowcount = 0)
begin
insert into t3 (info), 'this row not there:' + @c1 + ';' + cast(@c2,nvarchar(50))
end
fetch next form @sel into @c1, @c2
end
"do something like this, and it will get you what you want (data that is missing).
Please don't do that! The Except function will get you that data much more efficiently and rapidly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 1:10 pm
except is faster I concede, but unless your dealling with a million records, its good to learn about cursors.
January 6, 2009 at 1:21 pm
It's important to know about cursors. Mainly so you can fix them when other people have mistakenly used them for ... well ... almost anything.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 1:27 pm
I cannot believe you recommended a cursor to solve this issue. One of the previous posters already posted a way to do this using EXCEPT.
If you want to see what matches - use INTERSECTION.
And, if this needs to be done on a regular basis - go and buy a diff tool (Redgate SQL Data Compare or Apex SQL Diff are good choices).
Regardless - a cursor is not needed at all.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2009 at 1:49 pm
foxjazz (1/6/2009)
it's probably easier to use a cursor to iterate through your first table.
well deserving of a pork chop this one 😀
a simple select with a correlated sub query will do the trick as Ninja first posted and not a cursor in sight
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 6, 2009 at 1:56 pm
GSquared wrote:
When you say you want differences, what exactly do you mean?
Do you just mean rows that exist in one that don't exist in the other? Do you mean rows where the day number is the same, but the date is different, or where the date is the same but the day number is different? Something else?
- GSquared
Answer: Yes to both, I need to see if dates do not match and if day numbers do not match. When I said differences, I am only interested in rows that DO NOT match between the 2 tables (i.e. only the exceptions).
I did try using first Exception script(s) but could not run both with the "Then" in SQL Query window using SQL Server Management Studio. I had to run each part separately (Table A then Table B). The result was no different then running a Count statement to see if number of records are different. I probably did something wrong but would like to see the differences between the 2 tables in one result window.
January 6, 2009 at 2:05 pm
You can use EXCEPT to compare your tables:
SELECT {columns}
FROM db1.dbo.tablea
EXCEPT
SELECT {list of columns that match other select}
FROM db2.dbo.tableb
This will return a list of rows from tablea that do not have a matching row (based on the columns selected) from tableb. Reverse the statement to get the rows from tableb that do not have matching rows in tablea.
Review the subject in BOL for further information.
BTW - I recommend downloaded the trial edition of SQL Compare and SQL Data Compare from Redgate. I use both products on a regular basis and they do this kind of comparison a lot better than me trying to write something myself.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2009 at 2:09 pm
run the cursor and print the where clause that the cursor defines, and then you have both.
January 6, 2009 at 2:17 pm
rons (1/6/2009)
but would like to see the differences between the 2 tables in one result window.
then use the correlated query Ninja supplied above
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 6, 2009 at 2:48 pm
Thanks Jeff, the script below worked correctly.
Thanks again for everyone's assistance and answers to my question.
Jefferey Williams wrote:
You can use EXCEPT to compare your tables:
SELECT {columns}
FROM db1.dbo.tablea
EXCEPT
SELECT {list of columns that match other select}
FROM db2.dbo.tableb
This will return a list of rows from tablea that do not have a matching row (based on the columns selected) from tableb. Reverse the statement to get the rows from tableb that do not have matching rows in tablea.
January 6, 2009 at 2:56 pm
Glad we could help. You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply