• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

Is that possible to compare two SQL views and get the difference?

I want to compare two SQL Views in SQL Server 2005 and get the difference.
I have two views:
View1
View2
Compare View2 against View1 and get the unmatched values from View2.
Please help.
(I do not have sufficient permission to create constraints in my tables, so I created Views from the table).
Thanks and appreciate any help.
0
MM_OK
Asked:
MM_OK
  • 8
  • 5
  • 4
  • +2
2 Solutions
 
Patrick MatthewsCommented:
Please define what you mean by "unmatched".
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you mean the data or the view
0
 
MM_OKAuthor Commented:
Unmatched - Compare and get the differene (unmatched).

Angel: I mean the data

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Patrick MatthewsCommented:
MM_OK said:
>>Unmatched - Compare and get the differene (unmatched).

SELECT v2.*
FROM View2 v2LEFT JOIN
      View1 v1 ON v2.Col1 = v1.Col1 AND v2.Col2 = v1.Col2
WHERE v1.Col1 IS NULL

That returns all records from View2 where there is no match when looking at two columns.
0
 
SharathData EngineerCommented:
Check these scripts.
SELECT * FROM View1 WHERE ID NOT IN(SELECT ID FROM View2) 
 
SELECT * FROM View1 WHERE ID NOT IN(SELECT ID FROM View2 WHERE ID IS NOT NULL) 
 
SELECT * FROM View1 j WHERE j.ID NOT IN(SELECT ID FROM View2 n WHERE n.ID = j.ID) 
 
SELECT * FROM View1 j WHERE NOT EXISTS (SELECT 1 FROM View2 n WHERE n.ID = j.ID)
 
SELECT j.* 
  FROM View1 j 
  LEFT OUTER JOIN View2 n ON n.ID = j.ID 
 WHERE n.ID IS NULL 
-- Using Left Join
 
-- With a RIGHT Join you just switch the tables around
SELECT j.* 
  FROM View2 n 
 RIGHT OUTER JOIN View1 j ON n.ID = j.ID 
 WHERE n.ID IS NULL 
 
-- Full Join
SELECT j.* FROM View2 n 
  FULL OUTER JOIN View1 j ON n.ID = j.ID 
 WHERE n.ID IS NULL AND j.ID IS NOT NULL
 
-- Outer Apply
SELECT j.* FROM View1 j 
 OUTER APPLY (SELECT id FROM View2 n WHERE n.ID = j.ID) a
 WHERE a.ID IS NULL 
 
-- Except
SELECT * FROM View1
EXCEPT 
SELECT * FROM View2 

Open in new window

0
 
MM_OKAuthor Commented:
matthewspatrick:
SELECT v2.*
FROM View2 v2LEFT JOIN
      View1 v1 ON v2.Col1 = v1.Col1 AND v2.Col2 = v1.Col2
WHERE v1.Col1 IS NULL

That returns all records from View2 where there is no match when looking at two columns.
----
Thanks matthewspatrick. This query works when it looks at the specified 2 columns and find the difference between two views and bring the difference with regard to the columns defined.

But my requirement is something different.
I have View1 with 112 Fields
I have View2 with 112 Fields
(Both have identical Field Names)
View1 has the records keyed by Party1
View2 has the records keyed by Party2

Both parties are supposed to deliver same data. Taking View1 as the Control Set (assuming View1 is 100% pristine), I want to compare View2 against View1.

One of the fields keyed by both parties is DocumentNumber and the query should look at the DocumentNumber and the related 111 fields keyed by Party2 and should compare against the same DocumentNumber and the related 111 fields keyed by Party1.  Likewise the query should read each document number and the respective fields pertaining to it and should give the difference.
This should all happen in a single query.

I have very limited knowledge on the SQL side and is that something attainable or am I posing something impractical?
Thanks for your response.
0
 
SharathData EngineerCommented:
can you provide some sample data?
0
 
MM_OKAuthor Commented:
Sharath_123 said:
>> can you provide some sample data?

Please find the attached files.
In VIEW2-TEST SET, I highlighted the difference from VIEW2. The query should find the difference with reference to each document number.

Thanks in Advance.
VIEW1--CONTROL-SET-.xls
VIEW2--TEST-SET-.xls
0
 
Patrick MatthewsCommented:
MM_OK said:
>>But my requirement is something different.

It would have been nice for you to state your requirement in your actual question, or to have stated it when I
asked you to clarify things.  Instead, you left me to guess what your requirements were, which only wasted
my time and yours.
0
 
SharathData EngineerCommented:

My understanding is you want to check all the columns on both views and if there is difference in any of the column, you want that record.
Also you want the additional records from View 1.
So you can try like this. 
SELECT *
  FROM View1 v1
  LEFT JOIN View2 v2 
    ON v1.DOCNO = v2.DOCNO
   AND ISNULL(v1.DATASOURCECODE,'$@!') = ISNULL(v2.DATASOURCECODE,'$@!')
   AND ISNULL(v1.RECTYPE,'!') = ISNULL(v2.RECTYPE,'!')
   AND ISNULL(v1.COUNTY,'$@!') = ISNULL(v2.COUNTY,'$@!')
   AND ISNULL(v1.STATE,'$@!') = ISNULL(v2.STATE,'$@!')
   AND ISNULL(v1.PARTYCODE,'$@') = ISNULL(v2.PARTYCODE,'$@!')
   AND ISNULL(v1.NAMECODE,'$@') = ISNULL(v2.NAMECODE,'$@')
   AND ISNULL(v1.RECDATE,'2076-12-10') = ISNULL(v2.RECDATE,'2076-12-10')
   AND ISNULL(v1.BKNO,'9087') = ISNULL(v2.BKNO,'9087')
   AND ISNULL(v1.PGNO,'9087') = ISNULL(v2.PGNO,'9087')
   AND ISNULL(v1.DOCTYPE,'$@') = ISNULL(v2.DOCTYPE,'$@')
   AND ISNULL(v1.KeyerCode,'$@!') = ISNULL(v2.KeyerCode,'$@!')
 WHERE V2.DOCNO IS NULL
 
You can also try like this.
 
SELECT * FROM View1
EXCEPT
SELECT * FROM View2

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Have a look at the example below... it uses temp tables by way of example, but could easily be a view as well...

If you run it you will see what it is doing. Can easily adapt to your views if you want...  Look down after the inserts "where the real work begins"
-- step 1 create our test tables - just temp tables for now known as table_A and Table_B
create table #table_a 
    (id		     INT
    ,client      VARCHAR(40)
    ,ship        VARCHAR(40)
    ,voyage      VARCHAR(40)
    ,operation   VARCHAR(40)
    ,port        VARCHAR(40)
    ,eta         DATETIME
    ,etd         DATETIME
    ,operator    VARCHAR(40)
    ,id_ship     NUMERIC(15)
    ,id_port     NUMERIC(15)
    ,id_client   CHAR(5)
    ,id_operator NUMERIC(15)
    )
 
create table #table_b
    (id		     INT
    ,client      VARCHAR(40)
    ,ship        VARCHAR(40)
    ,voyage      VARCHAR(40)
    ,operation   VARCHAR(40)
    ,port        VARCHAR(40)
    ,eta         DATETIME
    ,etd         DATETIME
    ,operator    VARCHAR(40)
    ,id_ship     NUMERIC(15)
    ,id_port     NUMERIC(15)
    ,id_client   CHAR(5)
    ,id_operator NUMERIC(15)
    )
 
-- step 2 now populate with sample data with only one or two columns being different
 
insert #table_a values (1,'Client1','Ship1','Voyage1','operation1','port1','20090101','20090202','op1',1,1,1,1)
insert #table_b values (1,'Client1','Ship2','Voyage1','operation1','port1','20090102','20090202','op1',1,1,1,1)
insert #table_a values (2,'Client2','Ship3','Voyage3','operation1','port1','20090102','20090202','op2',1,1,1,1)
insert #table_b values (2,'Client2','Ship3','Voyage2','operation1','port1','20090102','20090202','op3',1,1,1,1)
 
-- now this is where the real work begins...
 
-- we will use dynamic SQL to populate it
 
DECLARE @sql varchar(max)
DECLARE @maxcol int
 
SET @maxcol = isnull((select max(colid) from tempdb..syscolumns where id = object_id('tempdb..#table_a','U')),0)   -- knowing how many columns...
 
IF @maxcol > 0
SELECT @sql = isnull(@sql,'') + 'select a.id, '+convert(varchar,colid)+' as col_id, '''+ name + ''' as Col_Name,' + 'convert(varchar,a.['+name+ ']) as Table_A, ' + 'convert(varchar,b.['+name+']) as Table_B 
from #table_a a left outer join #table_b b on a.id = b.id
where a.['+name+ '] <> b.['+name+']
--and any other conditions go here'+ case when colid < @maxcol then '
union all 
' else '
order by 1,2' end from tempdb..syscolumns where id = object_id('tempdb..#table_a','U') 
 
exec(@sql)
 
-- and then some tidy up
 
drop table #table_a
drop table #table_b

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
OK, so I imported your spreadsheets. There are duplicate DOCNO's in there - is there any unique key ? otherwise how do you expect to match individual rows ?

Or is that the real challenge ?   We might need to create a row_number() based subquery on those views to then use the row number as a basis for matching, or, concoct some level of "set" matching...

Anyway, be that as it may, there are a few more non-matching entries which are really just duplicates...

Same again - the interesting stuff really begins after the "create views" in step 3. Thought I would leave the load spreadsheets in just for interest...

-- step 1 load spreadsheets
 
SELECT * INTO TBL_CONTROL_SET FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\ee\VIEW1--CONTROL-SET.xls;HDR=YES', 'SELECT * FROM [sheet1$]')
GO
SELECT * INTO TBL_TEST_SET FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\ee\VIEW2--TEST-SET.xls;HDR=YES', 'SELECT * FROM [sheet1$]')
GO
 
-- step 2 create views
 
create view view_control_set
as
select * from tbl_control_set
GO
 
create view view_test_set
as
select * from tbl_test_set
GO
 
 
-- step 3 - now this is where the real work begins...
-- we will use dynamic SQL to compare row by row.
 
DECLARE @sql varchar(max)
DECLARE @maxcol int
 
SET @maxcol = isnull((select max(ordinal_position) from information_schema.columns where table_name = 'view_control_set'),0)   -- knowing how many columns...
 
IF @maxcol > 0
SELECT @sql = isnull(@sql,'') + 'select a.docno, '+convert(varchar,ordinal_position)+' as col_id, '''+ column_name + ''' as Column_Name,' + 'convert(varchar,a.['+column_name+ ']) as Control_Value, ' + 'convert(varchar,b.['+column_name+']) as Test_Sample 
from view_control_set a left outer join view_test_set b on a.docno = b.docno
where a.['+column_name+ '] <> b.['+column_name+']
--and any other conditions go here'+ case when ordinal_position < @maxcol then '
union all 
' else '
order by 1,2' end from information_schema.columns where table_name = 'view_control_set'
 
exec(@sql)

Open in new window

0
 
MM_OKAuthor Commented:
matthewspatrick:
>> It would have been nice for you to state your requirement in your actual question, or to have stated it when I asked you to clarify things.  Instead, you left me to guess what your requirements were, which only wasted my time and yours.

Apologies for the incovenience I created. But I really appreciate your help on helping me out with the LEFT JOIN. It helped me out in performing overall comparison. Thanks.

Sharath_123 - Thanks to you as well. I tried yours but I am not getting what I expected. The queries compare the views on the whole.

mark_wills: Thanks for laying out Dynamic SQL. I executed the query and I see the message as "command completed successfully'" but no results in tabular format. Hence, I inserted Select (@SQL) statement and it spit one field with NULL value in it. I am trying to work on the same query by extending some other conditions I want to see. Thanks for your help and much appreciated.
0
 
Mark WillsTopic AdvisorCommented:
Should be OK - well apart from some of that duplication - really do need to add something in there to identify different rows...  Also, changing the UNION ALL down toward the bottom to a straight UNION (ie remove three letters "all")  will return 29 rows instead of the 65 shown below.

If you can show how you have chnged it, or, the additional conditions then might be able to see where we need to do a bit more to make it work.

Anyway, Using those spreadsheets that you gave me I get :




Docno          Col_ID   Column_Name     Control_Value   Test_Sample
-------------------------------------------------------------------
3147615        6        PARTYCODE       GR              GE                                   -- clearly should not report but two docno in each view
3147615        6        PARTYCODE       GR              GE                                   -- with same values - it does not know which one to use.
3147615        6        PARTYCODE       GE              GR
3147615        6        PARTYCODE       GE              GR
3147615        7        NAMECODE        SM              ID
3147615        7        NAMECODE        SM              HW
3147615        7        NAMECODE        ID              SM
3147615        7        NAMECODE        ID              HW
3147615        7        NAMECODE        AK              SM
3147615        7        NAMECODE        AK              ID
3147615        7        NAMECODE        AK              HW
3147615        10       PGNO            441             421
3147615        10       PGNO            441             421
3147615        10       PGNO            441             421
3147615        10       PGNO            441             421
3147615        10       PGNO            441             421
3147615        10       PGNO            441             421
3147615        10       PGNO            441             421
3147615        10       PGNO            441             421
3147615        10       PGNO            441             421
3147615        12       KeyerCode       DP6             NO3
3147615        12       KeyerCode       DP6             NO3
3147615        12       KeyerCode       DP6             NO3
3147615        12       KeyerCode       DP6             NO3
3147615        12       KeyerCode       DP6             NO3
3147615        12       KeyerCode       DP6             NO3
3147615        12       KeyerCode       DP6             NO3
3147615        12       KeyerCode       DP6             NO3
3147615        12       KeyerCode       DP6             NO3
3147674        6        PARTYCODE       GR              GE
3147674        6        PARTYCODE       GE              GR
3147674        7        NAMECODE        HW              ID
3147674        7        NAMECODE        ID              HW
3147674        12       KeyerCode       DP6             NO3
3147674        12       KeyerCode       DP6             NO3
3147674        12       KeyerCode       DP6             NO3
3147674        12       KeyerCode       DP6             NO3
7251879        6        PARTYCODE       GR              GE
7251879        6        PARTYCODE       GR              GE
7251879        6        PARTYCODE       GE              GR
7251879        6        PARTYCODE       GE              GR
7251879        6        PARTYCODE       GE              GR
7251879        6        PARTYCODE       GE              GR
7251879        7        NAMECODE        EX              CO
7251879        7        NAMECODE        EX              ID
7251879        7        NAMECODE        EX              HW
7251879        7        NAMECODE        ID              CO
7251879        7        NAMECODE        ID              HW
7251879        7        NAMECODE        HW              CO
7251879        7        NAMECODE        HW              ID
7251879        7        NAMECODE        MM              CO
7251879        7        NAMECODE        MM              ID
7251879        7        NAMECODE        MM              HW
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3
7251879        12       KeyerCode       DP6             NO3

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Or, if we change our approaches slightly ...


-- change in approach to check if there are any other instances of that column_value for that docno (ie maybe a different row).
 
DECLARE @sql varchar(max)
DECLARE @maxcol int
 
SET @maxcol = isnull((select max(ordinal_position) from information_schema.columns where table_name = 'view_control_set'),0)   -- knowing how many columns...
 
IF @maxcol > 0
SELECT @sql = isnull(@sql,'') + 'select a.docno, '+convert(varchar,ordinal_position)+' as col_id, '''+ column_name + ''' as Column_Name,' + 'convert(varchar,a.['+column_name+ ']) as Control_Value, ' + 'convert(varchar,b.['+column_name+']) as Test_Sample 
from view_control_set a left outer join view_test_set b on a.docno = b.docno
where a.['+column_name+ '] <> b.['+column_name+']
and not exists (select NULL from view_test_set c where c.docno = a.docno and a.['+column_name+ '] = c.['+column_name+'])
--and any other conditions go here'+ case when ordinal_position < @maxcol then '
union 
' else '
order by 1,2' end from information_schema.columns where table_name = 'view_control_set'
 
exec(@sql)
 
 
-- or use #temp tables
 
select identity(int,1,1) as id, * into #table_a from view_control_set order by 2,3,4,5,6,7,8,9,10,11,12,13 --add where and any filtering, or column exclusions
select identity(int,1,1) as id, * into #table_b from view_test_set order by 2,3,4,5,6,7,8,9,10,11,12,13 --add where and any filtering, or column exclusions
 
DECLARE @sql varchar(max)
DECLARE @maxcol int
 
SET @maxcol = isnull((select max(colid) from tempdb..syscolumns where id = object_id('tempdb..#table_a','U')),0)   -- knowing how many columns...
 
IF @maxcol > 0
SELECT @sql = isnull(@sql,'') + 'select a.id, '+convert(varchar,colid)+' as col_id, a.docno, '''+ name + ''' as Col_Name,' + 'convert(varchar,a.['+name+ ']) as Table_A, ' + 'convert(varchar,b.['+name+']) as Table_B 
from #table_a a left outer join #table_b b on a.id = b.id and a.docno = b.docno
where (a.['+name+ '] <> b.['+name+'] or b.['+name+'] is NULL)
--and any other conditions go here'+ case when colid < @maxcol then '
union 
' else '
order by 1,3,2' end from tempdb..syscolumns where id = object_id('tempdb..#table_a','U') 
 
exec(@sql)
 
-- and then some tidy up
 
drop table #table_a
drop table #table_b

Open in new window

0
 
SharathData EngineerCommented:
>> I tried yours but I am not getting what I expected. The queries compare the views on the whole.

Better provide your expected result.
0
 
Mark WillsTopic AdvisorCommented:
Agree with Sharath - clearly stated output requirements and examples do make it easier to answer, and leaves out a lot of misunderstandings (as Sharath and I have recently experienced - for which I do owe Sharath an apology - Sharath was right in that case and I continued to display misunderstanding of the requirement )...
0
 
MM_OKAuthor Commented:
Please find the attachment where I have my expected result and other explanations.
Expected-Result.xls
0
 
Mark WillsTopic AdvisorCommented:
Well, goes close. Doesn't do that "stop" checking, and wasn't sure about partycode always appearing... T-SQL below lists all differences...


-- use temp tables to ensure correct alignment and reliable comparisons
 
select identity(int,1,1) as id, * into #table_a from view_control_set order by 2,3,4,5,6,7,8,9,10,11,12,13 --add where and any filtering, or column exclusions
select identity(int,1,1) as id, * into #table_b from view_test_set order by 2,3,4,5,6,7,8,9,10,11,12,13 --add where and any filtering, or column exclusions
 
-- use dynamic SQL to extract each column as either blank or a value from table_b if it is different
 
DECLARE @sql varchar(max)
 
SELECT @sql = isnull(@sql,'SELECT a.ID,a.DOCNO') + ', case when (a.['+name+ '] <> b.['+name+'] or b.['+name+'] is NULL) then convert(varchar,b.['+name+']) else '''' end as ['+name+']' 
from tempdb..syscolumns where id = object_id('tempdb..#table_a','U') and colid > 2
 
set @sql = @sql + '
from #table_a a left outer join #table_b b on a.id = b.id and a.docno = b.docno '
 
exec(@sql)
 
-- and then some tidy up
 
drop table #table_a
drop table #table_b

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
I for one provided a couple of working solutions. The data elements and disclosures changed along the way, but did provide working models up until each disclosure.

The last post did match the requirement, though there was an unanswered question about partycode.

There is some very good work in many of the postings in the thread above that is surely of some merit.

Because it is now redundant, does not mean that we did not contribute and work toward a solution back in February when it was abandoned by the Asker. To come back quite some months later to say it is "Obsolete" is not exactly a fair or righteous resolution.
0
 
Mark WillsTopic AdvisorCommented:
Thankyou MM_OK...
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 8
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now