Thursday, March 29, 2012

Comparing two SSIS Packages?

I'd like to know how people out there are comparing ("diffing") their DTSX files.

Using XML file compare doesn't seem to work, because the package XML appears to be arbitrarily ordered and reordered by the designer in Visual Studio.

ApexSQL is supposedly planning to have a tool available in Q3, but even their product page does not seem too hopeful: "Compare and Document SSIS Packages (may be released with ApexSQL Doc and ApexSQL Diff in Q3)"

I remember seeing a CodePlex project that was hoping to address this and other issues, but it was in its infancy, and I can't seem to locate it today.

I'm thinking of starting to write my own package compare tool that will work with the SSIS .NET API, but this seems like it's going to be a ridiculous amount of work and an exercise in pain, and it seems unlikely that I will make the time to target anything more than the specific portions of the package model that I really need.

So if anyone out there has words of wisdom to share, I'd love to hear it. And if (dare I get my hopes up?) have a URL to share, I'll buy you a beer at TechEd in Orlando week after next.

Thanks in advance!

No words of wisdom to share, but I agree on the need for this kind of tool. We've been looking into rolling our own tools as well, but creating anything of general applicability would be a major project.|||

I don't know if there is a clever way of comparing packages. Perhaps you should open a suggestion in SQL Server connect site

http://connect.microsoft.com/site/sitehome.aspx?SiteID=68

[Microsoft follow-up]

|||

Thanks for the feedback, Rich and Rafael. I'll definitely post this as a suggestion in Connect, but...

Is no one out there (or more specifically, no one here on the SSIS forums) doing anything to compare or diff SSIS packages? I honestly wasn't expecting anyone to say "oh yeah, I use XXX tool, it does a great job" but I was expecting something...

To take this thread in a somewhat different direction, if I were to start a CodePlex project with the aim of developing a package diff utility based on the SSIS object model, would anyone else be interested in contributing? I'll bet I'm not the only SSIS ETL guy out there with a C# background...

|||

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

Thank you!

This was the CodePlex project I mentioned in my original post - I stumbled across it once, but could not find it again when I started this thread here.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

So... what are you using to compare packages, today?

|||

I try not to Smile

Usually I use a text difference tool and deal with the headaches of elements being in different order.

|||

jwelch wrote:

I try not to Smile

Usually I use a text difference tool and deal with the headaches of elements being in different order.

Well, it's good to know that great minds do indeed think alike.

|||

Can I just add my voice to the need for such a tool. I still use DTS Compare for our 2000 installations and something similar for SSIS would be of great use.

Comparing two SQLServer databases

Hi, does anybody know a nice tool to automatically compare and show
only the structural differences between two databases, this is, the
differences in the columns of a table, constrains, data type, etc.

Thanks

J.A.I use this one (there are a number of other, simipar products):

http://www.red-gate.com/SQL_Compare.htm

Simon|||For excellent compare functionality (including data) plus the ability
to link your source control system into the whole process I recommend
looking at DB Ghost (www.dbghost.com). It also guarantees any delta
script produced will work (due to a unique algorithm), something no
other tool can promise.

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.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|||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)|||NickName (dadada@.rock.com) 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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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 (dadada@.rock.com) 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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||NickName (dadada@.rock.com) 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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.|||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).|||NickName (dadada@.rock.com) 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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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..Table1 ORDER BY MyPK" queryout
"C:\temp\Table1.txt" /T /c /SMyServer

BCP "SELECT * FROM MyDatabase..Table2 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" <dadada@.rock.com> wrote in message
news:1104161334.766126.171470@.f14g2000cwb.googlegr oups.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).|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> 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..Table1 ORDER BY MyPK" queryout
> "C:\temp\Table1.txt" /T /c /SMyServer
> BCP "SELECT * FROM MyDatabase..Table2 ORDER BY MyPK" queryout
> "C:\temp\Table2.txt" /T /c /SMyServer
> WINDIFF "C:\temp\Table1.txt" "C:\temp\Table2.txt"

However, the use of windiff will break down if there is a column which is
permitted to be different in the tables. But there is a better alternative:
Beyond Compare, from http://www.scootersoftware.com. Beyond Compare
offers comparison on character level.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

You're exactly right. Maybe they did that to test my analytic skill,
who knows :)|||Dan,

Thanks. It seems a good way to try. Will do tomorrow.

Erland, no columns are supposed to contain different data sets though
one table may have an extra column, that is supposedly the only
difference. OK, I'll try the recommended software as well when get a
chance. I appreciate it.

Donsqlsql

Comparing two rows in a Table

Hi Gurus,
Is there any automated way to compare the contents of TWO rows in the same
table of SQL Server ?
Thanks in advance.
Regards,
YogWhat do mean by "Automated"? There is certainly a way, just write a query
that returns a boolean 1/0 based on whether the row column values are the
same or different... How to "Automate" it, depends on what "event" you want
to "trigger", (cause ) it to run... If yo want it to run automatically on
some time interval, use SQLAgent...
If you want it to run whenever someone inserts, updates, or deletes a record
from the table, then use a trugger.
"Yog" wrote:

> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the same
> table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog|||What result do you want from the comparison? Do you mean you want to
compart two particular rows? In that case you can use a self-join on
the columns you want to compare.
Maybe you just want to find duplicates, in which case:
SELECT col1, col2, col3, ...
FROM YourTable
GROUP BY col1, col2, col3, ...
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||Yog wrote:
> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the
> same table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog
You can use the CHECKSUM() function as in:
Create table #checktest(col1 int, col2 int, col3 int)
insert into #checktest values (1, 2, 2)
insert into #checktest values (2, 2, 2)
insert into #checktest values (3, 2, 3)
Select CHECKSUM(col2, col3) as "ID 1"
From #checktest
where col1 = 1
UNION ALL
Select CHECKSUM(col2, col3) as "ID 2"
From #checktest
where col1 = 2
UNION ALL
Select CHECKSUM(col2, col3) as "ID 3"
From #checktest
where col1 = 3
drop table #checktest
David Gugick
Imceda Software
www.imceda.com|||To precisely put it , the content of the problem is as below
Objective :- Compare TWO rows contents for few selected Columns. If they are
different then show those columns with the contents.
ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
language or front end)
"CBretana" wrote:
> What do mean by "Automated"? There is certainly a way, just write a quer
y
> that returns a boolean 1/0 based on whether the row column values are the
> same or different... How to "Automate" it, depends on what "event" you wa
nt
> to "trigger", (cause ) it to run... If yo want it to run automatically on
> some time interval, use SQLAgent...
> If you want it to run whenever someone inserts, updates, or deletes a reco
rd
> from the table, then use a trugger.
> "Yog" wrote:
>|||SELECT A.col1, A.col2, ...
FROM YourTable AS A,
YourTable AS B
WHERE A.x = ?
AND B.x = ? /* Specify the two rows to be compared */
AND
(A.col1<>B.col1
OR A.col1<>B.col1
OR ...)
David Portas
SQL Server MVP
--|||Hi David,
Many thanks for your POST.
Your answer provided me HINT to achieve my goal of comparing two rows for my
requirement.
Regards,
Yog
"David Portas" wrote:

> SELECT A.col1, A.col2, ...
> FROM YourTable AS A,
> YourTable AS B
> WHERE A.x = ?
> AND B.x = ? /* Specify the two rows to be compared */
> AND
> (A.col1<>B.col1
> OR A.col1<>B.col1
> OR ...)
> --
> David Portas
> SQL Server MVP
> --
>

Comparing two rows in a Table

Hi Gurus,
Is there any automated way to compare the contents of TWO rows in the same
table of SQL Server ?
Thanks in advance.
Regards,
Yog
On Thu, 17 Mar 2005 10:19:02 -0800, Yog wrote:

>Hi Gurus,
>Is there any automated way to compare the contents of TWO rows in the same
>table of SQL Server ?
Hi Yog,
The answer is probably yes - but you've given too little information
about your needs to allow for any more specific information.
Please check out www.aspfaq.com/5006to find out what information is
needed (and in what form) to allow others to understand your problem and
proposes solutions.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Well,
To precisely put it , the content of the problem is as below
Objective :- Compare TWO rows contents for few selected Columns. If they are
different then show those columns with the contents.
ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
language or front end)
Hope this makes sence.
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 10:19:02 -0800, Yog wrote:
>
> Hi Yog,
> The answer is probably yes - but you've given too little information
> about your needs to allow for any more specific information.
> Please check out www.aspfaq.com/5006to find out what information is
> needed (and in what form) to allow others to understand your problem and
> proposes solutions.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 17 Mar 2005 22:27:02 -0800, Yog wrote:

>Well,
>To precisely put it , the content of the problem is as below
>Objective :- Compare TWO rows contents for few selected Columns. If they are
>different then show those columns with the contents.
>ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
>language or front end)
>Hope this makes sence.
Hi Yog,
Yes - but not enough to provide actual help. I need your table structure
(posted as CREATE TABLE stmt), some sample data (as INSERT stmts) and
the expected output.
I see that the link I posted got mangled - I missed a space between the
link and the next word. Here's the link again:
www.aspfaq.com/5006
Please follow that link and read the information there; then post the
required information here for further help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Comparing two row values

whats the best way to compare two row values and concatenate those two

For example

Table1
---
Col1 Col2
----
A 1
A 4
B 2
C 3
A 5

I need a query to return something like
"where (A=1 or A=4 or A=5) and (b=2) and (c=3)"

Thanks in advancetry using cursor. one single SQL will not do the job.|||If I understand what you're asking, there is no need to concatenate, and certainly no need for a cursor.

What I think you want to do is SELECT rows from a table or view based on the values of four different columns (a, b, c, and d). In your case, you need to see the rows that meet all three criteria: Column A values of 1, 4, or 5 and also have Column B = 2 and Column c = d.

If I understand your question, then I'd use:SELECT *
FROM myTable
WHERE (a = 1 OR a = 4 OR a = 5)
AND b = 2
AND c = d-PatP|||You need to explain more. The way I see it you want
select * from Table1
where (Col1='A' and Col2 in (1,4,5))
or (Col1='B' and Col2=2)
or (Col1='C' and Col2=3)|||Ok let me elaborate

I have a table with two columns with the values

Col1 Col2
----
A 1
A 4
B 2
C 3
A 5

The data mentioned above is just the sample, I dunno wats its going to be in

I am looking for a query and that should form the where clause like
"where (A=1 or A=4 or A=5) and (b=2) and (c=3)"

which means it should find the identical ones first and concate with or clause and rest join with the and clause. In this case we have three A's so it should form (A=1 or A=4 or A=5) and then and clause to (b=2) and (c=3)

Thanks in advance|||I think I get what you're after.

Where do you need to plug in this "where" clause when you're done? Perhaps there's a cleaner way to get the job done.|||this only froms the string that u r looking for using cursor (i can hear some screaming!!). u know better what to do with that string

declare @.Str char(1)
declare @.LastStr char(1)
declare @.Num smallint
declare @.Out varchar(200)
set @.Out = '('
declare Cur1 cursor for select Str,Num from theTable order by str,num
open Cur1
fetch next from Cur1 into @.Str,@.Num
while @.@.fetch_status = 0
begin
if (@.LastStr is not null)
begin
if(@.LastStr <> @.Str)
set @.Out = @.Out + ') and ('
else
set @.Out = @.Out + ' or '
end
set @.Out = @.Out + @.Str + '='+ cast(@.Num as varchar)
set @.LastStr = @.Str
fetch next from Cur1 into @.Str,@.Num
end
close Cur1
deallocate Cur1
set @.Out = 'where ' + @.Out + ')'
print @.Out|||thanks upalsen, but I am thinking is there any other way to do this without cursors. Your code definetly works but looking for alternative. Thanks|||as i told earlier, it is possible without cursor as well. but that is neither going to look smarter not would perform better. performance of course is dependent on many other factors that is not clear to us form the post. if it is a onetime affair u can use cursor without any doubt. however, if u r still interested in cursor-less sql, let me know.|||thanks upalsen, yes please let me know how can we do this without cursors, I did use the cursors and its working fine, but just want to know how can we do without cursors. I appreciate your help.

Thanks in advance.|||select Col1,Col2 into #TT from theTable order by Col1,Col2
update #TT set #TT.Col1 = #TT.Col1+'_Max' /*expecting the width sufficient to hold 5 chars, else add col/change width*/ from #TT, (select Col1,max(Col2) as MaxCol2 from #TT group by Col1) as TT2 where #TT.Col1 = TT2.Col1 and #TT.Col2=TT2.MaxCol2
declare @.sql varchar(200)
set @.sql = '('
select @.sql = @.sql + left(Col1,1) + '=' + cast(Col2 as varchar) + case when len(Col1)>1 then ') and (' else ' or ' end from #TT
set @.sql = 'where ' + left(@.sql,len(@.sql)-6)
print @.sql
drop table #TT

looks meaningless though...cant find anything better than this....|||I like a challenge. I came up with this one...
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp (Col1 CHAR(1), Col2 CHAR(1))
INSERT #tmp VALUES ('A','1')
INSERT #tmp VALUES ('A','4')
INSERT #tmp VALUES ('B','2')
INSERT #tmp VALUES ('C','3')
INSERT #tmp VALUES ('A','5')
INSERT #tmp VALUES ('C','1')

DECLARE @.sql VARCHAR(1000)
SET @.sql = '('

SELECT @.sql = @.sql + CASE
WHEN Col1 IS NULL THEN ''
WHEN Col2 IS NULL THEN ') AND ('
ELSE Col1 + '=' + Col2 + ' OR '
END
FROM #tmp
GROUP BY Col1, Col2
WITH ROLLUP

SELECT REPLACE(LEFT(@.sql, LEN(@.sql) - 6), ' OR )', ')')

comparing two records field by field

Hi,
a stored proc should find all different fields in two records. This two
records are in two temp tables and have the same fields, but the
recordstructure can be different in every call of the stored proc.
For example, in one call i check the difference of two records from the
customer table, in the next call i check the difference of two records from
the orders table.
So i cannot use field names. In the moment i read all informations from
sysobjects and syscolumns and build sql strings for each column and execute
them to get the value as varchar and compare them.
But this is so performance- and timeconsuming, especially for records with
nearly 200 fields, that i have to find another way.
Any suggestions how to do this?
thanks for all tips,
HelmutIt depends on what your ultimate aim is (other than pain). If you're
just wanting some data analysis there's a tool called SQL data compare
that does that quite well.
If you're aiming for some nice dynamic query, you'll have to post a SQL
example (I'm afraid your description was a tad cryptic).
my approach when comparing things dynamically is to use the stored proc
sp_columns. Shove that up a temporary table, use sp_executesql to then
execute the compare, however it sounds like you're nearly doing that
(shouldn't be too bad performance wise - are you hitting the cursors a
bit too hard?)
I think you'll get better advice if you put some hard examples up of
what you're currently doing, and what you're wanting it to do.
As a load of people write as a generic response - "post your DDL and
code examples and maybe we can help more" - they say "more" as though
that was in some way helpful
anyway,
more info dude
Cheers
Will|||Hello Will,
thanks for your response. Here an example:
let's say, i have two backups of a database. Now i restore both backups and
have a stored proc wich loops through the persons table record by record.
There is an identity field, so i can read the same record from both backups
...
insert into #tbl1 select * from backup1.dbo.persons where id = 123
insert into #tbl2 select * from backup2.dbo.persons where id = 123
...
if both records exists, then i want to find the differences field by field,
for this i call another stored proc, and this proc knows, that it has one
record in #tbl1 and another record in #tbl2 with identical structure.
But it does not know, that this are records from table persons!
So it should do something like:
declare @.tblDiff (
fieldname varchar(20),
oldValue varchar(2000),
newValue varchar(2000) )
for x = 1 to #tbl1.fieldcount do begin
if (#tbl1.field[x].IsNull and not #tbl2.field[x].IsNull) or
(not #tbl1.field[x].IsNull and #tbl2.field[x].IsNull) or
(#tbl1.field[x].Value <> #tbl2.field[x].Value) then
insert into @.tblDiff values(#tbl1.field[x].Name, #tbl1.field[x].Value,
#tbl2.field[x].Value)
end
return select * from @.tblDiff
This is a pseudo code to show what i want to do. Now i need to solve this
in pure TSQL.
thanks,
Helmut|||You could try something like this, but it sounds like you're already at
this solution.
DECLARE @.Table varchar(50)
SET @.Table = 'sysobjects'
DECLARE @.IDvalue int
CREATE TABLE #Cols
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
DATA_TYPE smallint,
TYPE_NAME sysname,
[PRECISION] int,
LENGTH int,
SCALE smallint,
RADIX smallint,
NULLABLE smallint,
REMARKS varchar(254),
COLUMN_DEF nvarchar(4000),
SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint
)
CREATE TABLE #Results(field sysname, OldValue nvarchar(4000), NewValue
nvarchar(4000))
INSERT INTO #Cols
exec sp_Columns @.Table
DECLARE Cols Cursor
FOR SELECT Colname
FROM #Cols
DECLARE @.Colname sysname
OPEN Cols
FETCH NEXT FROM cols INTO @.COlname
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE @.SQL nvarchar(4000)
SET @.SQL = 'INSERT INTO #Results SELECT ''' + @.Colname + ''', T1.' +
@.Colname + ', T2.' + @.ColName + ' FROM database1.dbo.' + @.tablename + '
as T1 INNER JOIN database2.dbo.' + @.TableName + ' as T2 on T1.IDField =
' + @.IDValue + ' AND T2.IDField = ' + @.IDValue + 'WHERE t1.' + @.Colname
+ ' != T2.' + @.Colname + ' AND NOT (T1.' + @.ColName + ' IS NULL AND
T2.' + @.ColName + ' IS NULL)'
exec sp_executesql @.SQL
FETCH NEXT FROM cols INTO @.COlname
END
CLOSE Cols
DEALLOCATE Cols
DROP TABLE #Cols
SELECT * FROM #REsults
DROP TABLE #Results|||Another soloution would be to use
BINARY_CHECKSUM()
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Am 6 Apr 2006 01:47:28 -0700 schrieb Jens:

> Another soloution would be to use
> BINARY_CHECKSUM()
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
Not really, at first, BOL says, that binary_checksum() can detect most, but
not all changes. And second, this can be used maybe in the calling function
to detect, if a difference is here. But my problem is the second step -
finding and documenting the changes.
bye, Helmut|||Hi Will,
thanks for this source. What i see at first, i need much more steps to find
the differences, so maybe you solution is faster (i hope).
But the main points are the same, information comes from sysobjects and for
every field i have to build a statement and do a sp_executesql.
But on the wend i take this source and make a benchmark and post the
result on monday.
Do you think, it would be much faster using an external stored proc?
thank you very much,
Helmut|||What are you trying to do Helmut? some kind of a retrospective audit
trail? have you considered putting triggers on your tables to maintain
this data as it changes?|||Am 6 Apr 2006 03:07:00 -0700 schrieb Will:

> What are you trying to do Helmut? some kind of a retrospective audit
> trail? have you considered putting triggers on your tables to maintain
> this data as it changes?
Yes, it should do both. For audit trail i use the data from inserted and
deleted. Working with columns_updated() is no solution, because i only
store changes and this means, if i have a record with 200 fields and only 5
fields are filled with data (and the rest is NULL), then i store only this
5 values. But when you do an insert, then columns_updated() has all bits
set.
bye, Helmut|||Helmut,
I'm afraid I think that your query is just about as fast as you'll get
it (I certainly can't think of any significant ways to improve it).
I've had one idea you could try, but I don't have time to fully
investigate it:
You could rather than performing the difference check on each column,
build up the SQL string as 'select inserted.'+colname+',
deleted.'+colname,+' CAST(CASE when insert.'+colname+' !=
deleted.'+colname+' then 1 else 0 end as bit) as ' + colname +
'ischanged' .
then build up this string for the whole set of columns, only do one
select into a temporary table, then do your inserts into your audit
table based on whether or not the bit field conameischanged has been
set to 1.
I have no idea if that will work, it has the advantage that you only
select from your 2 tables once as opposed to once per column, but it
has the divantage of needing an extra insert into a temporary table.
if you do write it then post the source and the benchmarks, I'd be
interested to know how it performs. If this isn't clear let me know,
I'll try and write some SQL this evening.
Cheers
Will

comparing two integers and returning a third

I am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:

Table1.SomeNumber1 = 1

Table1.SomeNumber2 = 2

Table2.SomeNumber1 = 2

Table2.SomeNumber2 = 1

I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.

I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.

Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.

Gmz

CREATE TABLE [dbo].[num1](
[id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL,
[flag1] [int] NULL,
[flag2] [int] NULL
)
CREATE TABLE [dbo].[num2](
[id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL
)
--After the comparison, two flag fields in table num1 are updated
UPDATE num1
SET flag1=(SELECT (CASE WHEN a.number1 = b.number1 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id), flag2=(SELECT (CASE WHEN a.number2 = b.number2 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id)
FROM num1 c|||

I took what you posted and applied it to just return the values of the comparisons as results of my query which saved me from having to store the extra data in the table. It worked great.

Thank you!!!!

GMZ

sqlsql

comparing two fields

Greetings.

I am trying to write a sql that identifies all the rows in one table that do not match another. The sql needs to compare the values in two columns of each table. In the past, I've used a where not exists and a subquery to identify missing rows.

select a.block_code

from BLKPTS_ADDXY a

where not exists

(select block_code

from allblkzips as s

where s.block_code = a.block_code)

I need to add a second column from each table (zip) to the query. For some reason, it doesn't seem as straightforward as I thought it should be. Suggestions are appreciated.

Thanks.

alan

If you are using SQL 2005, try a EXCEPT JOIN. Something like:

SELECT Block_Code, Zip
FROM BLKPTS_ADDXY
EXCEPT
SELECT Block_Code, Zip
FROM allblkzips

|||I usually use a not in clause

I m pretty sure this will work..you can concat the two columns thus,

select convert(varchar(10),a.block_code)+convert(varchar(10),a.second_code), <anything else you want to select>

from BLKPTS_ADDXY a

where convert(varchar(10),a.block_code)+convert(varchar(10),a.second_code) not in

(

selectconvert(varchar(10),s.block_code)+convert(varchar(10),s.second_code)

from allblkzips as s

)

This will probably run faster than the query you are using which tries to run a where condition in an inner query with a field in the outer query, comparable with a for followed by another v/s a nested for loop in procedural programming.|||

Do either of the queries below return the results you are expecting?

Chris

SELECT a.block_code

FROM BLKPTS_ADDXY a

WHERE NOT EXISTS

(SELECT block_code

FROM allblkzips s

WHERE s.block_code = a.block_code

AND s.zip = a.zip)

SELECT a.block_code

FROM BLKPTS_ADDXY a

WHERE NOT EXISTS

(SELECT block_code

FROM allblkzips s

WHERE s.block_code = a.block_code)

OR NOT EXISTS

(SELECT block_code

FROM allblkzips s

WHERE s.zip = a.zip)

|||

I prefer the "outer join" method, since it helps enforce thinking in terms of INNER and OUTER JOINs.

select

a.block_code

from BLKPTS_ADDXY a

left outer join allblkzips s

on a.block_code = s.block_code and

a.zip = s.zip

where (s.zip is null) -- you only need to specify one column here, since the JOIN fails if both do not match

Dan

Comparing two dimensions

Hi, I'm writing an MDX query in VS2003 (using RS2000) with AS2000 and I want to know how can I just display the mesaures for the members of a dimension that are equal to another dimension. For example, If I display dimension1 and dimension2 I get (not all of the members in both dimensions are equal):

Cat - Cat
Cat - Dog
Cat - Car
Cat - Horse
Dog - Cat
Dog - Dog
Dog - Car
Dog - Horse
Car- Cat
Car- Dog
Car- Car
Car- Horse
Horse - Cat
Horse - Dog
Horse - Car
Horse - Horse

But I really want to get:

Cat - Cat
Dog - Dog
Car - Car
Horse - Horse

I've tryed using FILTER and I can't make it work, need help here. When I try to use INTERSECT I get the following error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Calculation error - unknown error (urn:schemas-microsoft-com:xml-analysis)

BUTTONS:

OK

Filter is the right way to go. somethign like the following should work

Select Filter( {<dim1.hier1.level1>.Member * <dim2.hier2.level2>.Members}, <dim1.hier1.CurrentMember.Name = dim2.hier2.CurrentMember.Name ) on 0 from <cube>

Note that this is a bit of an unusual request and won't perform well on large dimensions as joins are normally built into the structure of the cube rather than done at run time.

|||It worked, thanks a lot man. I'm starting to learn MDX and it's quite amazing.

Comparing two different databases

Hi all,
Sql server 7

i have two databases best and books.Most of the tables present in best are in books except some.
i have been given a task to list out the tables and columns of which are present in best database and not in books database.

Pls suggest me the simplest and quickest way to do this.
this is very urgent.

waiting for reply.
TIA
AdilI'd just run selects from sysobjects and syscolumns, which are SQL Server's method of storing object definitions. Others on this forum will probably suggest selecting from the SCHEMA tables.|||Check LEFT OUTER JOIN in BOL, this should be very trivial (hint: ...no, t's too trivial ;) )|||hi

thnks for reply

can u tell me as to how i can select columns of a particular table using syscolumns

thansks once again|||Originally posted by aadil
hi

thnks for reply

can u tell me as to how i can select columns of a particular table using syscolumns

thansks once again

You could use linked servers. Query below will return list of tables from server1 if these are no the same column(s) on server2. Checking for object owner also is included.

select 'User table ['+su.name+'.'+so.name+'] does not have column ['+sc.name+']'
from server1.dbo.sysobjects so
join server1.dbo.syscolumns sc on sc.id=so.id
join server1.dbo.sysusers su on su.uid=so.uid
where so.xtype='U'
and exists(select 'ok' from server2.dbo.sysobjects r
join server2.dbo.sysusers sur on sur.uid=r.uid and sur.name=su.name
where xtype='U' and r.name=so.name)
and not exists(select 'ok' from server2.dbo.sysobjects sor
join server2.dbo.sysusers sul on sul.uid=sor.uid and sul.name=su.name
join server2.dbo.syscolumns scr on scr.id=sor.id
where sor.xtype='U' and sor.name=so.name and scr.name=sc.name)'

Comparing two date periods for overlapping

hi guys,

i have a booking table which has the following columns...

booking
--------------
dCheckin (format 11/9/2006 12:00:00 AM)
dCheckout (format 11/11/2006 12:00:00 AM)

when a new booking is entered, we want to make sure that the period entered does not conflict with an existing record.

not sure how to go about building the query required. any help would be greatly appreciated.

mikeDo periods that "touch" count as an overlap? Do you need to consider rooms, customers, or anything else for an overlap, or is all of the data in the table the same?

-PatP|||it does matter if they are touching eg. someone cannot checkin during a period already occupied. the other data is in another table.

m.|||ahh sorry patp. i see what you mean. touching yes it does matter. a person cannot checkin on the day someone checks out.

mike|||CREATE TABLE #patp (
id INT IDENTITY
, dCheckin DATETIME
, dCheckout DATETIME
)

INSERT INTO #patp (
dCheckin, dCheckout
) SELECT '2006-01-01', '2006-01-10'
UNION ALL SELECT '2006-01-15', '2006-01-20'
UNION ALL SELECT '2006-02-01', '2006-02-10'
UNION ALL SELECT '2006-02-10', '2006-02-15'
UNION ALL SELECT '2006-03-01', '2006-03-10'
UNION ALL SELECT '2006-03-02', '2006-03-07'
UNION ALL SELECT '2006-04-01', '2006-04-10'
UNION ALL SELECT '2006-04-08', '2006-04-13'

SELECT *
FROM #patp AS a
WHERE EXISTS (SELECT *
FROM #patp AS b
WHERE b.id != a.id -- Never compare a row with itself
AND (b.dCheckin <= a.dCheckout -- A starts before B ends
AND a.dCheckin <= b.dCheckout)) -- B ends after A starts
ORDER BY a.dCheckin

DROP TABLE #patp-PatP

comparing two datasets

Hello - I would like to perform something lika a "diff" between two datasets
in my report.
Lets say that DS1 holds all customers and DS2 holds customers owning a car.
How do I find them customers not owning a car?
DavidPerhaps you could create a 3rd data set that would be some combination of the
queries used to create the 1st and 2nd data sets?
DS1 -
select customername
from customers
DS2 -
select name
from carowners
DS3 -
select customername
from customers
where customername in (
select name
from carowners
)
just a thought... i am not sure whast your schema looks like - there may be
a simpler solution or my solution may be too simple.
--
~lb
"David" wrote:
> Hello - I would like to perform something lika a "diff" between two datasets
> in my report.
> Lets say that DS1 holds all customers and DS2 holds customers owning a car.
> How do I find them customers not owning a car?
> David
>|||Problem is that one of the datasets is tabular data from a textfile, I
cannot use SQL to query it, but otherwise your answer still helps me to
define the problem better, thx :)
Sorry for not pointing out the flat file-stuff in the first place.
David
"lonnye" <lonnye@.discussions.microsoft.com> wrote in message
news:B3B4E2AF-A516-44EC-BE2E-EF5D98EC83AD@.microsoft.com...
> Perhaps you could create a 3rd data set that would be some combination of
> the
> queries used to create the 1st and 2nd data sets?
> DS1 -
> select customername
> from customers
> DS2 -
> select name
> from carowners
> DS3 -
> select customername
> from customers
> where customername in (
> select name
> from carowners
> )
> just a thought... i am not sure whast your schema looks like - there may
> be
> a simpler solution or my solution may be too simple.
> --
> ~lb
>
> "David" wrote:
>> Hello - I would like to perform something lika a "diff" between two
>> datasets
>> in my report.
>> Lets say that DS1 holds all customers and DS2 holds customers owning a
>> car.
>> How do I find them customers not owning a car?
>> Davidsqlsql

Comparing two databases for deleted records

hi ,
there is a tool that do compare between 2 databases and display deleted records an changes in schema and data ,
it's friendlly tool with beautiful gui for users.
it is called dbMaestro.
You can find it here:
http://www.extreme.co.il
You might want to check out the Red-Gate tools SQL Compare and SQL
DataCompare. I think these tools will do what you are requesting. Here is
their website: http://www.red-gate.com/
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"yaniv cohen" <yanivc@.extreme.co.il> wrote in message
news:30D974F8-F7EC-4AD7-BCC7-DDE5D9146096@.microsoft.com...
> hi ,
> there is a tool that do compare between 2 databases and display deleted
records an changes in schema and data ,
> it's friendlly tool with beautiful gui for users.
> it is called dbMaestro.
> You can find it here:
> http://www.extreme.co.il
>

Comparing two databases for deleted records

How do I compare two databases to determine deleted
records."Aboki" <anonymous@.discussions.microsoft.com> wrote in message
news:13d7001c41b24$a4d4d240$a001280a@.phx
.gbl...
> How do I compare two databases to determine deleted
> records.
Select *
from dbArchive.dbo.table1 as a
left outer join
dbUpdated.dbo.table1 as u
on a.PK = u.PK
where u.pk is null
dbArchive = the old database name
dbUpdated = the one with the rows deleted
PK = whatever the Primary Key column is
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||hi ,
there is a tool that do compare between 2 databases and display deleted reco
rds an changes in schema and data ,
it's friendlly tool with beautiful gui for users.
it is called dbMaestro.
You can find it here:
http://www.extreme.co.il|||You might want to check out the Red-Gate tools SQL Compare and SQL
DataCompare. I think these tools will do what you are requesting. Here is
their website: http://www.red-gate.com/
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"yaniv cohen" <yanivc@.extreme.co.il> wrote in message
news:30D974F8-F7EC-4AD7-BCC7-DDE5D9146096@.microsoft.com...
> hi ,
> there is a tool that do compare between 2 databases and display deleted
records an changes in schema and data ,
> it's friendlly tool with beautiful gui for users.
> it is called dbMaestro.
> You can find it here:
> http://www.extreme.co.il
>

Comparing two databases

Are there any tools that allow you to compare two sql server databases to
find out what is different between them (the database structure, including
tables, relationships etc)? Thanks,
- Gabe
http://www.red-gate.com/sql/summary.htm
AMB
"Gabe Matteson" wrote:

> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
>
|||Gabe Matteson wrote:
> Are there any tools that allow you to compare two sql server
> databases to find out what is different between them (the database
> structure, including tables, relationships etc)? Thanks,
> - Gabe
There are numerous schema comparison tools available. Change Manager
from Imceda/Quest Software available at www.imceda.com.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Seconding this one...I've been using Red-gate SQL and Data compare for 2
years...fantastic products...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E8C28859-7C90-4FD6-A40B-3490F7172846@.microsoft.com...[vbcol=seagreen]
> http://www.red-gate.com/sql/summary.htm
>
> AMB
> "Gabe Matteson" wrote:
|||Thanks!
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
|||ErWin and ER-Studio do this as well.
Greg Jackson
PDX, Oregon
|||I agree. SQL Compare from Red Gate is brilliant.
"Kevin3NF" wrote:

> Seconding this one...I've been using Red-gate SQL and Data compare for 2
> years...fantastic products...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:E8C28859-7C90-4FD6-A40B-3490F7172846@.microsoft.com...
>
>
|||you may want to look at a complete approach to change management, from your
favourite source control to your target database - DB Ghost
http://www.dbghost.com & the scripts produced always work - it's all in the
way they're produced.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Gabe Matteson" wrote:

> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
>
|||I use red-gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
|||when red-gate fails (script out put has errors), DB Ghost succeeds which is
why most of our customers used to be red-gate customers.
free for MVP's, educational and non-profit organizations.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Wayne Snyder" wrote:

> I use red-gate
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
> news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
>
>

Comparing two databases

Is there a way to compare the strored procedure,views and UDF's between two
databases to see if there are any differences. I use one database for
developement and the other is online. I would like to be able to run a
structural comparison between the two to make sure i didn't forget to script
a function or stored procedure after making modifications. I normally just
script all objects from developement to online after mods but I would like t
o
know for certain they are both the same sometimes. Also I would like to read
up on best practices for tracking developement so if you know of any good
reading that would help me. I use MS Access project as a front end and SQL
2000 as the Be. Thanks> Is there a way to compare the strored procedure,views and UDF's between
> two
> databases to see if there are any differences. I use one database for
> developement and the other is online. I would like to be able to run a
> structural comparison between the two to make sure i didn't forget to
> script
> a function or stored procedure after making modifications.
SQL Compare 4.0
http://www.red-gate.com/|||"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:3A5AF648-8D41-441A-BA91-0EF14B5C09A0@.microsoft.com...
> Is there a way to compare the strored procedure,views and UDF's between tw
o
> databases to see if there are any differences. I use one database for
We use SQL Delta. Does wonders for our spare time! (maintaining/updating app
rox. 25 DB
installations)
http://www.sqldelta.com|||You should be checking your development and production scripts into some
type of source version control system. For example, Visual Source Safe has
an option to compare two projects and list files that are different, and it
has a feature for comparing two versions of a script side by side with
differences highlighted.
Also, you can script the databases to seperate folders and use a tool like
WinMerge to perform the comparisons:
http://groups.google.com/group/micr...br />
46abfa76
Rather than scripting all objects from development to production in bulk,
you need to identify specific objects that have changed and deploy them
individually. There are a number of reasons, but for one, you run the risk
of accidentally running a script that drops / recreates a table thus
resulting in data loss.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:3A5AF648-8D41-441A-BA91-0EF14B5C09A0@.microsoft.com...
> Is there a way to compare the strored procedure,views and UDF's between
> two
> databases to see if there are any differences. I use one database for
> developement and the other is online. I would like to be able to run a
> structural comparison between the two to make sure i didn't forget to
> script
> a function or stored procedure after making modifications. I normally just
> script all objects from developement to online after mods but I would like
> to
> know for certain they are both the same sometimes. Also I would like to
> read
> up on best practices for tracking developement so if you know of any good
> reading that would help me. I use MS Access project as a front end and SQL
> 2000 as the Be. Thanks

Comparing two databases

Are there any tools that allow you to compare two sql server databases to
find out what is different between them (the database structure, including
tables, relationships etc)? Thanks,
- Gabehttp://www.red-gate.com/sql/summary.htm
AMB
"Gabe Matteson" wrote:

> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
>|||Gabe Matteson wrote:
> Are there any tools that allow you to compare two sql server
> databases to find out what is different between them (the database
> structure, including tables, relationships etc)? Thanks,
> - Gabe
There are numerous schema comparison tools available. Change Manager
from Imceda/Quest Software available at www.imceda.com.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Seconding this one...I've been using Red-gate SQL and Data compare for 2
years...fantastic products...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E8C28859-7C90-4FD6-A40B-3490F7172846@.microsoft.com...[vbcol=seagreen]
> http://www.red-gate.com/sql/summary.htm
>
> AMB
> "Gabe Matteson" wrote:
>|||Thanks!
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>|||ErWin and ER-Studio do this as well.
Greg Jackson
PDX, Oregon|||I agree. SQL Compare from Red Gate is brilliant.
"Kevin3NF" wrote:

> Seconding this one...I've been using Red-gate SQL and Data compare for 2
> years...fantastic products...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:E8C28859-7C90-4FD6-A40B-3490F7172846@.microsoft.com...
>
>|||you may want to look at a complete approach to change management, from your
favourite source control to your target database - DB Ghost
http://www.dbghost.com & the scripts produced always work - it's all in the
way they're produced.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Gabe Matteson" wrote:

> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
>|||I use red-gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>|||when red-gate fails (script out put has errors), DB Ghost succeeds which is
why most of our customers used to be red-gate customers.
free for MVP's, educational and non-profit organizations.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Wayne Snyder" wrote:

> I use red-gate
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
> news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
>
>sqlsql

Comparing two databases

Are there any tools that allow you to compare two sql server databases to
find out what is different between them (the database structure, including
tables, relationships etc)? Thanks,
- Gabe
http://www.red-gate.com/sql/summary.htm
AMB
"Gabe Matteson" wrote:

> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
>
|||Gabe Matteson wrote:
> Are there any tools that allow you to compare two sql server
> databases to find out what is different between them (the database
> structure, including tables, relationships etc)? Thanks,
> - Gabe
There are numerous schema comparison tools available. Change Manager
from Imceda/Quest Software available at www.imceda.com.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Seconding this one...I've been using Red-gate SQL and Data compare for 2
years...fantastic products...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E8C28859-7C90-4FD6-A40B-3490F7172846@.microsoft.com...[vbcol=seagreen]
> http://www.red-gate.com/sql/summary.htm
>
> AMB
> "Gabe Matteson" wrote:
|||Thanks!
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
|||ErWin and ER-Studio do this as well.
Greg Jackson
PDX, Oregon
|||I agree. SQL Compare from Red Gate is brilliant.
"Kevin3NF" wrote:

> Seconding this one...I've been using Red-gate SQL and Data compare for 2
> years...fantastic products...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:E8C28859-7C90-4FD6-A40B-3490F7172846@.microsoft.com...
>
>
|||you may want to look at a complete approach to change management, from your
favourite source control to your target database - DB Ghost
http://www.dbghost.com & the scripts produced always work - it's all in the
way they're produced.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Gabe Matteson" wrote:

> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
>
|||I use red-gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
|||when red-gate fails (script out put has errors), DB Ghost succeeds which is
why most of our customers used to be red-gate customers.
free for MVP's, educational and non-profit organizations.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Wayne Snyder" wrote:

> I use red-gate
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
> news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
>
>

Comparing two databases

Hi,
I would like to compare two sql server databases.
I need to find the difference in the two database(i.e table, colums,
data type)
Is there any free utility available to compare sql server database
I'm using sql server 2000.
Help me out to compare databases

Thanks & Regards,
ManiIt's not free, but you can download a 14-day trial version;
http://www.redgate.com/products/SQL_Compare/index.htm
Markus|||Another one is AlfaAlfa's SQL Server Comparison Tool (SCT)

http://www.sql-server-tool.com
- also not free, but it has 30 days of free evaluation period, which
can be extended to 90 days. Comparisons can be fully automated
through the usage of command line parameters.

SCT works with SQL Server 2005, 2000 and 7.0 and between these
versions.

Dariusz Dziewialtowski.

Comparing two databases

Are there any tools that allow you to compare two sql server databases to
find out what is different between them (the database structure, including
tables, relationships etc)? Thanks,
- Gabehttp://www.red-gate.com/sql/summary.htm
AMB
"Gabe Matteson" wrote:
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
>|||Gabe Matteson wrote:
> Are there any tools that allow you to compare two sql server
> databases to find out what is different between them (the database
> structure, including tables, relationships etc)? Thanks,
> - Gabe
There are numerous schema comparison tools available. Change Manager
from Imceda/Quest Software available at www.imceda.com.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Seconding this one...I've been using Red-gate SQL and Data compare for 2
years...fantastic products...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E8C28859-7C90-4FD6-A40B-3490F7172846@.microsoft.com...
> http://www.red-gate.com/sql/summary.htm
>
> AMB
> "Gabe Matteson" wrote:
>> Are there any tools that allow you to compare two sql server databases to
>> find out what is different between them (the database structure,
>> including
>> tables, relationships etc)? Thanks,
>> - Gabe
>>|||Thanks!
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>|||ErWin and ER-Studio do this as well.
Greg Jackson
PDX, Oregon|||I agree. SQL Compare from Red Gate is brilliant.
"Kevin3NF" wrote:
> Seconding this one...I've been using Red-gate SQL and Data compare for 2
> years...fantastic products...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:E8C28859-7C90-4FD6-A40B-3490F7172846@.microsoft.com...
> > http://www.red-gate.com/sql/summary.htm
> >
> >
> > AMB
> >
> > "Gabe Matteson" wrote:
> >
> >> Are there any tools that allow you to compare two sql server databases to
> >> find out what is different between them (the database structure,
> >> including
> >> tables, relationships etc)? Thanks,
> >> - Gabe
> >>
> >>
> >>
>
>|||you may want to look at a complete approach to change management, from your
favourite source control to your target database - DB Ghost
http://www.dbghost.com & the scripts produced always work - it's all in the
way they're produced.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Gabe Matteson" wrote:
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>
>|||I use red-gate
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>|||when red-gate fails (script out put has errors), DB Ghost succeeds which is
why most of our customers used to be red-gate customers.
free for MVP's, educational and non-profit organizations.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Wayne Snyder" wrote:
> I use red-gate
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
> news:OdFamzndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> > Are there any tools that allow you to compare two sql server databases to
> > find out what is different between them (the database structure, including
> > tables, relationships etc)? Thanks,
> > - Gabe
> >
>
>|||'www.red-gate.com' (http://www.red-gate.com) or 'www.dbghost.com
(http://www.dbghost.com) are two software use to compare the databas
--
gheman
----
ghemant's Profile: http://www.msusenet.com/member.php?userid=234
View this thread: http://www.msusenet.com/t-187055366|||I use http://www.adeptsql.com SQL Diff Tool because it was so much
faster than red-gate.
Tim S|||Ive seen people script DBs and use "Beyond Compare" as well.
Greg Jackson
PDX, Oregon|||I use SQL Effects Clarity from http://www.sqleffects.com mostly because
of the side by side twin views of the schemas that lets you drill down
to the most minute differences.
Gabe Matteson wrote:
> Are there any tools that allow you to compare two sql server databases to
> find out what is different between them (the database structure, including
> tables, relationships etc)? Thanks,
> - Gabe
>

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

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
Shub
INSERT 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.googlegr oups.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.googlegro ups.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
sqlsql

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|||I would try something like this:
-- groups that do not have exact matchselect
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i
where not
i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1)
or not
i.col1_cnt = (select count(*) from tableB
where tableB.colB = i.col1)|||I think this is going to work. I have tried some example and its
looking like its doing what I want.
Thank you very much for your input and time Alexander.
Alexander Kuznetsov wrote:
> I would try something like this:
> -- groups that do not have exact matchselect
> i.col1
> from
> (select col1, count(*) col1_cnt from tableA group by col1) i
> where not
> i.col1_cnt = (select count(*) from tableA
> join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
> where tableA.col1 = i.col1)
> or not
> i.col1_cnt = (select count(*) from tableB
> where tableB.colB = i.col1)|||Ok I have a similar problem that I am unable to figure it out.
Please run the following script that will create table tabA and tabA
with few rows that will help me illustate my issue.
Basically I am trying to update col3 in tabB with the value of colc in
tabA. In tabA there are two sets of rows that are identified by unique
value in colc. So the fisrt two rows are one set and the last three are
another set. The rows in tabB should match the first two rows in tabA.
Can anyone please help me with this update statement. I havce tried few
different things but I am unable to come with a solution
Thanks in advance
Shub
--*******************************************
select 100 as cola, 1 as colb, 1 as colc
into tabA
insert tabA
Select 100,2,1
insert tabA
Select 100,1,2
insert tabA
Select 100,2,2
insert tabA
Select 100,3,2
select 100 as col1, 1 as col2, 0 as col3
into tabb
insert tabb
select 100,2,0
select * from taba order by cola,colc,colb
select * from tabb
--***********************************************|||how should tabb look like after the update?|||After the update tabB should have values of 1 for col3. Thanks for
looking into it Alexander.|||you welcome. Try this:
update tabb set col3 = matches.colc
from tabb,
(select colc, count(*) c from tabA where cola=100 group by colc)
all_taba_groups,
(select colc, count(*) c from tabA, tabB
where cola=100 and col1=100 and colb=col2
group by colc) matches
where col1=100 and all_taba_groups.colc = matches.colc and
all_taba_groups.c = matches.c
with all the usual warnings than update ... from will not tell you if
there is an ambiguity (i.e. more than one matching colc)|||Thanks and that works great, however I do not think I could hardcode
100 because there could be other sets present in both tables. Sorry my
example did not illustrate that. Here please run this script.
After the update I want values of 1 for col3 in tabb where col1= 100
and
values of 4 in col3 in tabb where col1 = 200
--******************************************
select 100 as cola, 1 as colb, 1 as colc
into tabA
insert tabA
Select 100,2,1
insert tabA
Select 100,1,2
insert tabA
Select 100,2,2
insert tabA
Select 100,3,2
insert taba
select 200, 1,3
insert taba
select 200, 2,3
insert taba
select 200, 1,4
insert taba
select 200, 2,4
insert taba
select 200, 3,4
select 100 as col1, 1 as col2, 0 as col3
into tabb
insert tabb
select 100,2,0
Insert tabb
select 200,1,0
Insert tabb
select 200,2,0
Insert tabb
select 200,3,0
select * from taba order by cola,colc,colb
select * from tabb order by col1,col2
--***************************************************|||try this one:
update tabb set col3 = matches.colc
from tabb,
(select colc, cola, count(*) c from tabA group by colc, cola)
all_taba_groups,
(select colc, cola, count(*) c from tabA, tabB
where cola=col1 and colb=col2
group by colc, cola) matches
where tabb.col1=matches.cola
and all_taba_groups.cola = matches.cola
and all_taba_groups.colc = matches.colc
and all_taba_groups.c = matches.c|||Actually I want col3 to be updated with 4 for all rows in tabb where
col1= 200.|||select * from tabb
update tabb set col3 = matches.colc
from tabb,
(select colc, cola, count(*) c from tabA group by colc, cola)
all_taba_groups,
(select col1, count(*) c from tabB group by col1) all_tabB_groups,
(select colc, cola, count(*) c from tabA, tabB
where cola=col1 and colb=col2
group by colc, cola) matches
where tabb.col1=matches.cola
and all_taba_groups.cola = matches.cola
and all_taba_groups.colc = matches.colc
and all_taba_groups.c = matches.c
and all_tabB_groups.col1 = matches.cola
and all_tabB_groups.c = matches.c
select * from tabb order by col1
--drop table tabA
--drop table tabB|||You daaa man. That is exactly what I wanted. Thanks for all your help,
it is greatly appreciated.|||Alexander,
I have run into a situation with this first query you helped me with.
Basically I want a query to report if there is any mismatch of set of
groups between tabaleA and tableB. When I run the query you helped me
with in this example it returns the value 10 but it really should not
return any rows in this instance because there is no mismatch of groups
between these two tables. This query worked in everyother case except
this. Here is the script to create tableA and TableB with data in it.
You will notice that there is no mismatch but the query is returning
10.
--**************************************
--Creates tablea
select 10 as col1,'A' as col2
into tablea
insert tablea
select 10,'B'
insert tablea
select 10,'C'
insert tablea
select 30,'X'
insert tablea
select 30,'Y'
insert tablea
select 40,'A'
insert tablea
select 40,'B'
insert tablea
select 40,'C'
--tableB
Select 1 as cola,10 as colb,'A' as colc
into tableb
insert tableb
select 1,10,'B'
insert tableb
select 2,30,'X'
insert tableb
select 2,30,'Y'
insert tableb
select 3,40,'A'
insert tableb
select 3,40,'B'
insert tableb
select 3,40,'C'
insert tableb
select 4,10,'A'
insert tableb
select 4,10,'B'
insert tableb
select 4,10,'C'
select * from tablea order by col1,col2
select * from tableB order by cola,colb,colc
-- groups that do not have exact matchselect
select
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i
where not
i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1)
or not
i.col1_cnt = (select count(*) from tableB
where tableB.colB = i.col1)|||in fact the original query displays groups that have mismatches:
select
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i
where not
i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1)
or not
i.col1_cnt = (select count(*) from tableB
where tableB.colB = i.col1)
10 has both a match (4) and a mismatch(1)
the query that shows groups that don't have matches is slightly
different:
select * from tableA
where col1 not in(
-- groups that have exact matches
select
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i,
(select colA, colB, count(*) colB_cnt from tableB group by colA, colB)
j
where i.col1 = j.colB
and i.col1_cnt = j.colB_cnt
and i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1 and tableB.cola = j.colA)
)
BTW what do you need all this stuff for.
Is it online dating service looking for a perfect match?|||You are right about the first query and I see how you are looking at
it. However let me expalin what I am trying to do. By the way I want to
thank you for all your help on this it is greatly appreciated.
Basically my project is to allow telephone companies to set up a group
of telephone services and call it a Super Duper Bundle. Customers
could then select a group of customised services from that super duper
Bundle and still call it super duper Bundle. So if the Super duper
Bundle consisted of three different services Voice Mail, CallerID,
Three way calling the possible difeerent sets of Super Duper Bundle
could be consisting of
UniqueID 1 Super Duper Bundle- Voice Mail, CallerID
unique ID 2 Super Duper Bundle- Voice Mail, Three way Calling
unique ID 3 Super Duper Bundle- Caller Id, Three way calling
unique ID 4 Super Duper Bundle -CallerId, Three way calling, Voice
Mail.
So before I create a new bundle set (one of the above 4 combinations)
in this case, I need a query to see if it already exist in the
database. So in my example
TableA consists of all the diferent combination that already exist in
the database.
IDs refer to colA, Superduper Bundle refers to colb and different
services are represented in Colc with individual rows. So the rows in
tableA are grouped together by cola to define a combination.
TableB consists of another set of combination of this Bundle that I
need to check to see if it already exist in the database.
So if you run
select
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i
where not
i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1)
or not
i.col1_cnt = (select count(*) from tableB
where tableB.colB = i.col1)
I should not get anything back because the combinations as a group
already exist in tableA, there is no mismatch.
Let me know if you can come up with a solution....
Thanks Again
Shub