Comparing two data sets betweeen two tables

I am using Sql Server 7 and here is what I am wanting to do. I want to
be able to compare a data set from table A to see a exact match of data
set exist in table B, if it does not exist then I want to go ahead and
add the data set to tableB.
Here is an example I want check and see if sets of rows from table A =
sets of rows from TableB. If they do not match I would then go ahead
and add that set from tableA to tableB.
In my example the set in tableA is grouped by COL1 and COL2 and the set
in tableB is grouped by COLB and COLC.
So in this example 10-A,10-B do not exist in TableB. So I should be
able to add it to Table B.
TableA
COL1 COL2
10 A
10 B
30 X
30 Y
TableB
COLA COLB COLC
1 10 A
1 10 B
1 10 C
2 30 X
2 30 Y
Any help in this regard will be greatly appreciated. I am trying to
avoid cursors to achieve this.
Thanks
ShubINSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE colb = TableA.col1
AND colc = TableA.col2) ;
David Portas
SQL Server MVP
--|||Hi David,
I am just curious to ask that why SQL Server do not have set
operater MINUS , INTERSECT implemented, which can be implemented by
using query as you did for MINUS operator
If MINUS were implemented the query would be lot simpler
Insert into tableA select * from TableA Minus Select * From TableB
With Warm regards
Jatinder Singh|||EXISTS = INTERSECT
NOT EXISTS = MINUS
Or is it the spelling your concerned about?
Nik Marshall-Blank MCSD/MCDBA
<jatinder.singh@.clovertechnologies.com> wrote in message
news:1126248735.883313.245060@.f14g2000cwb.googlegroups.com...
> Hi David,
> I am just curious to ask that why SQL Server do not have set
> operater MINUS , INTERSECT implemented, which can be implemented by
> using query as you did for MINUS operator
> If MINUS were implemented the query would be lot simpler
> Insert into tableA select * from TableA Minus Select * From TableB
>
> With Warm regards
> Jatinder Singh
>|||I do appreciate your quick response and apologise for not explaining my
situation clearly in my first post. I have added few lines of code that
will create and insert the rows for my examples.
After you run the script I provided to create and insert the rows in
TableA and TableB, if I run your query it does not insert rows 10-A and
10-B (First two rows) into TableB. But it really should because
although 10-A,10-B,10-C exist in table B, no set of just 10-A and 10-B
exist in tableB. I am referring to sets in tableB by uniqe value in
COLA in table B. So in my example there are really are two different
sets in table.
--Creates TableA and inserts the rows for my example
select 10 as 'col1','A' as 'col2' into tableA
insert tableA
select 10,'B'
insert tableA
select 30,'X'
insert tableA
select 30,'Y'
--Creates TableB and inserts the rows for my example
select 1 as 'cola',10 as 'colB','A' as colC into tableB
insert tableB
Select 1, 10, 'B'
insert tableB
Select 1, 10, 'C'
insert tableB
Select 2, 30, 'X'
insert tableB
Select 2, 30, 'Y'
--Displays the content of both the tables
select * from tableA order by col1,col2
Select * from tableB order by cola,colb,colc
--This is not acomplishing what I am wanting to achieve
INSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE colb = TableA.col1
AND colc = TableA.col2)
Thanks again for your time.
Shub|||You can also do a
select checksum_agg(binary_checksum(*)) from [Table_Name]
on both tables. You should get the same value if data is the same.
Microsoft claims that this is not absolutely one-hundred-percent perfect as
it is possible for two different tables to return the same value based on
the ascii character values; however, this is unlikely and the method has
always worked for me.
walt
<shubtech@.gmail.com> wrote in message
news:1126209114.244231.60810@.g49g2000cwa.googlegroups.com...
>I am using Sql Server 7 and here is what I am wanting to do. I want to
> be able to compare a data set from table A to see a exact match of data
> set exist in table B, if it does not exist then I want to go ahead and
> add the data set to tableB.
> Here is an example I want check and see if sets of rows from table A =
> sets of rows from TableB. If they do not match I would then go ahead
> and add that set from tableA to tableB.
> In my example the set in tableA is grouped by COL1 and COL2 and the set
> in tableB is grouped by COLB and COLC.
> So in this example 10-A,10-B do not exist in TableB. So I should be
> able to add it to Table B.
> TableA
> COL1 COL2
> 10 A
> 10 B
> 30 X
> 30 Y
> TableB
> COLA COLB COLC
> 1 10 A
> 1 10 B
> 1 10 C
> 2 30 X
> 2 30 Y
>
> Any help in this regard will be greatly appreciated. I am trying to
> avoid cursors to achieve this.
> Thanks
> Shub
>|||not sure if that is what you wanted:
create table t1(i int identity, j int)
insert into t1(j) values(1)
insert into t1(j) values(2)
insert into t1(j) values(3)
create table t2(i int identity, j int)
insert into t2(j) values(1)
insert into t2(j) values(2)
insert into t2(j) values(4)
-- rows in t1 that do not have exact match in t2
select * from t1 t
-- there is a row in t2 with the same PK
where exists(select * from t2 where t2.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2
i j
-- --
3 3
(1 row(s) affected)
-- rows in t2 that do not have exact match in t1
select * from t2 t
-- there is a row in t1 with the same PK
where exists(select * from t1 where t1.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2
i j
-- --
3 4
(1 row(s) affected)
drop table t1
drop table t2
and yes, it's easier to accomplish using MINUS|||SQL Server 2005 has the ANSI operators INTERSECT and EXCEPT. You can
also do a "minus join" in SQL2000:
INSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
LEFT JOIN TableB
ON TableB.colb = TableA.col1
AND TableB.colc = TableA.col2
WHERE TableB.colb IS NULL ;
David Portas
SQL Server MVP
--|||> ON TableB.colb = TableA.col1
> AND TableB.colc = TableA.col2
well If I have more than a hundred columns I need to generate some
really long SQL like this
AND TableB.col121 = TableA.col121
AND TableB.col122 = TableA.col122
kinda boring, is it not? I'd rather have SQL Server do the job for me,
using UNION...|||This is not exactly what I am looking. In my example I should be able
to insert rows
10 A
10 B
because 10 A and 10 B do not exist as a group defined by COlA in table
B
So I should still be able to insert 10 A and 10 B rows with a different
ID for COLA in table B.
After that is inserted into the table I should no longer be able to
insert this into tableB the only other possible rows that I could
insert into table B for my given example would be
10 B
10 C
Hope this make sense. I know I am not explaining it so well.
I do appreciate your time.
Alexander Kuznetsov wrote:
> not sure if that is what you wanted:
> create table t1(i int identity, j int)
> insert into t1(j) values(1)
> insert into t1(j) values(2)
> insert into t1(j) values(3)
> create table t2(i int identity, j int)
> insert into t2(j) values(1)
> insert into t2(j) values(2)
> insert into t2(j) values(4)
> -- rows in t1 that do not have exact match in t2
> select * from t1 t
> -- there is a row in t2 with the same PK
> where exists(select * from t2 where t2.i = t.i)
> -- but some other columns are different
> and
> (select count(*) from
> (
> select * from t1
> union
> select * from t2
> )t_both where t_both.i = t.i
> ) = 2
> i j
> -- --
> 3 3
> (1 row(s) affected)
>
> -- rows in t2 that do not have exact match in t1
> select * from t2 t
> -- there is a row in t1 with the same PK
> where exists(select * from t1 where t1.i = t.i)
> -- but some other columns are different
> and
> (select count(*) from
> (
> select * from t1
> union
> select * from t2
> )t_both where t_both.i = t.i
> ) = 2
>
> i j
> -- --
> 3 4
> (1 row(s) affected)
>
> drop table t1
> drop table t2
> and yes, it's easier to accomplish using MINUS