473,699 Members | 2,701 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Comparing two sets of data

I have the following situation. One set of data has 274 rows (set2)
and anther has 264 (set1). Both data sets are similar in structure as
well as values for both of them were extracts from the same parent
table. Hope the info would substitute DDL. I need to find the "gap"
rows between these two sets.
Attempted to run a query like
select count(*)
from set2
where not exists
(select *
from set1)
did not yield what I desired. What else to try?

TIA.

Jul 23 '05 #1
12 5153
nib
NickName wrote:
I have the following situation. One set of data has 274 rows (set2)
and anther has 264 (set1). Both data sets are similar in structure as
well as values for both of them were extracts from the same parent
table.
Hope the info would substitute DDL.
It doesn't.
I need to find the "gap"
rows between these two sets.
Attempted to run a query like
select count(*)
from set2
where not exists
(select *
from set1)
did not yield what I desired. What else to try?


Try posting your DDL. We at least need column names and keys to help you
here.

Zach
Jul 23 '05 #2
OK,

I've proven that the "EXISTS" keyword/function can't solve this
problem. But then what?

-- test equality between two data sets
-- desired resultset: return "gap" rows

-- DDL and DML
create table #tmp1 (col1 int, col2 char(1));

insert into #tmp1
values(1,'a')
insert into #tmp1
values(2,'b')
insert into #tmp1
values(3,'c')
insert into #tmp1
values(4,'d')
insert into #tmp1
values(5,'e');

select * into #tmp2
from #tmp1
where col1 < 5;
select *
from #tmp1
where not exists
(select *
from #tmp2)

Jul 23 '05 #3
NickName (da****@rock.co m) writes:
I've proven that the "EXISTS" keyword/function can't solve this
problem. But then what?


It certainly can, but you must specify how the NOT EXISTS is to work.
SQL is not about telepathy.

For your repro, you could do

select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t2
WHERE t1.col1 = t2.col1)

or

select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2)

depending on what you are looking for. The first just lists missing key
values, the second list all mismatches.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
Thanks, Erland.
*) laziness is an enemy, I thought about that but the table has a large
number of columns, so, was wondering if there's another approach. Will
try it tomorrow when the db is available.
*) btw, I like the word, "telepathy" .

Erland Sommarskog wrote:
NickName (da****@rock.co m) writes:
I've proven that the "EXISTS" keyword/function can't solve this
problem. But then what?
It certainly can, but you must specify how the NOT EXISTS is to work.
SQL is not about telepathy.

For your repro, you could do

select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t2
WHERE t1.col1 = t2.col1)

or

select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2)

depending on what you are looking for. The first just lists missing

key values, the second list all mismatches.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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


Jul 23 '05 #5
NickName (da****@rock.co m) writes:
Thanks, Erland.
*) laziness is an enemy, I thought about that but the table has a large
number of columns, so, was wondering if there's another approach. Will
try it tomorrow when the db is available.


If your aim is to find differences in any column, you will indeed have
to write code that has all column. There is no shortcut. What you can
do, if you have many tables and columns, is to generate code by reading
metadata. But this requires that you have a clear understanding for which
columns you want to compare, and which you do not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
Erland,

Something seems odd. Here's two test cases. Both are successful.
-- test equality between two data sets (with exact meta data)
-- desired resultset: return "gap" rows

-- DDL and DML
create table #tmp1 (col1 int, col2 char(1));

insert into #tmp1
values(1,'a')
insert into #tmp1
values(2,'b')
insert into #tmp1
values(3,'c')
insert into #tmp1
values(4,'d')
insert into #tmp1
values(5,'e');
select * into #tmp2
from #tmp1
where col1 < 5;

select *
from #tmp1 t1
where not exists
(select *
from #tmp2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)

-- outcome = success

-- test equality between two data sets
-- with minor meta data difference (one table has 3 attributes while
the other has 2)
-- note: comparison seems still successful
-- desired resultset: return "gap" rows

drop table #tmp1
drop table #tmp2;

-- DDL and DML
create table #tmp1 (col1 int, col2 char(1),col3 int);

insert into #tmp1
values(1,'a',11 )
insert into #tmp1
values(2,'b',22 )
insert into #tmp1
values(3,'c',33 )
insert into #tmp1
values(4,'d',44 )
insert into #tmp1
values(5,'e',55 );

select col1,col2 into #tmp2
from #tmp1
where col1 < 5;

select *
from #tmp1 t1
where not exists
(select *
from #tmp2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)

-- outcome = success

HOWEVER, when I applied the above to my tables (sorry I can't post
exact structure nor data here, PARENT table has 13 columns and the
derived table has 12 of them, comparison is between them), sql still
failed to find the "gap". What could possibly stands in the way?
Many thanks.

Jul 23 '05 #7
Ahe, I think I've found the problem. In "my" tables, both has
duplicate rows, however, the number of duplicate rows are not the same,
(distinct rows are sure the same).

Jul 23 '05 #8
NickName (da****@rock.co m) writes:
Ahe, I think I've found the problem. In "my" tables, both has
duplicate rows, however, the number of duplicate rows are not the same,
(distinct rows are sure the same).


I'm not sure how this should be addressed. Are you saying that in
one table you have two rows with the same value, but in another you
only have one?

If there are no disctinct keys in the data, all relational operations will
be problematic, that's for sure.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9
You might find it easier to export the table contents as ordered data and
then use a text file compare utility. Command-prompt example:

BCP "SELECT * FROM MyDatabase..Tab le1 ORDER BY MyPK" queryout
"C:\temp\Table1 .txt" /T /c /SMyServer

BCP "SELECT * FROM MyDatabase..Tab le2 ORDER BY MyPK" queryout
"C:\temp\Table2 .txt" /T /c /SMyServer

WINDIFF "C:\temp\Table1 .txt" "C:\temp\Table2 .txt"

--
Happy Holidays

Dan Guzman
SQL Server MVP

"NickName" <da****@rock.co m> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .
Ahe, I think I've found the problem. In "my" tables, both has
duplicate rows, however, the number of duplicate rows are not the same,
(distinct rows are sure the same).

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
2004
by: Dan Stromberg | last post by:
We will soon have 3 copies, for testing purposes, of what should be about 4.5 terrabytes of data. Rather than cmp'ing twice, to verify data integrity, I was thinking we could speed up the comparison a bit, by using a python script that does 3 reads, instead of 4 reads, per disk block - with a sufficiently large blocksize, of course. My question then is, does python have a high-level API that would facilitate this sort of thing, or...
5
4823
by: Skip Montanaro | last post by:
I'd like to compare two xml.dom.minidom objects, but the naive attempt fails: >>> import xml.dom.minidom >>> d1 = xml.dom.minidom.parse("ES.xml") >>> d2 = xml.dom.minidom.parse("ES.xml") >>> d1 == d2 False My goal is to decide whether or not I need to prompt the user to save config information at the end of a program run by generating a minidom object then
26
10169
by: William Park | last post by:
How do you compare 2 strings, and determine how much they are "close" to each other? Eg. aqwerty qwertyb are similar to each other, except for first/last char. But, how do I quantify that? I guess you can say for the above 2 strings that - at max, 6 chars out of 7 are same sequence --> 85% max
41
3944
by: Odd-R. | last post by:
I have to lists, A and B, that may, or may not be equal. If they are not identical, I want the output to be three new lists, X,Y and Z where X has all the elements that are in A, but not in B, and Y contains all the elements that are B but not in A. Z will then have the elements that are in both A and B. One way of doing this is of course to iterate throug the lists and compare each of the element, but is there a more efficient way? ...
19
2649
by: Dennis | last post by:
I have a public variable in a class of type color declared as follows: public mycolor as color = color.Empty I want to check to see if the user has specified a color like; if mycolor = Color.Empty then..... or if mycolor is Color.Empty then .......
11
11414
by: John Salerno | last post by:
I'd like to compare the values in two different sets to test if any of the positions in either set share the same value (e.g., if the third element of each set is an 'a', then the test fails). I have this: def test_sets(original_set, trans_letters): for pair in zip(original_set, trans_letters): if pair == pair: return False
2
3345
by: Yash | last post by:
Hi, We are in the process of tuning the performance of our stored procs in SQL 2000 and are looking for a tool that would help us in comparing the result sets of an old SP and a modified SP. The tool should take as input the names of the 2 SPs, a set of parameter values and invoke them. Internally it should compare the result sets returned and log/ display the difference in result sets if any. We do not want to compare response time,...
0
991
by: DomiNeug | last post by:
Hello, Since a while i have to find a way of comparing "Sets" (multiple int Values) and so to find equal sets. There simply 3 tables ValueList with: ID int ValueListHasValue: ID int, ValueListID int, ValueID int Value: ID int, Description varchar(max) The sets are defined in the ValueList has ValueTable. Any combination and order is possible as there is also no restriction on the amount of Values in a ValueList. My first approach was to...
3
1658
by: Sean Dalton | last post by:
Hello, I have a two sets OLDLIST and REMOVE. I would like to remove every element in OLDLIST if it is also occuring in REMOVE and store the remaining elements from OLDLIST into NEWLIST. So far, I have read in both lists but I'm struggling comparing the elements
0
8686
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8615
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9033
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8911
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8882
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6533
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5872
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4375
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
2009
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.