Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Comparing two tables or views easily

Author  Topic 

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-15 : 21:46:17
Based on the discussion at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23038

and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!

The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.


CREATE PROCEDURE CompareTables(@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS

-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)


- Jeff

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-07 : 21:37:37
Great Stuff jeff, exactly what I was looking for.


Thanks,
Kevin
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 2007-07-23 : 16:49:02
how do i write a sync procedure that syncs both tables and ensures that Table_2 keeps all services that are listed in Table_1

how do i write a procedure for the sync between both tables, and put this procedure into the package

so basically there have to be two sync parts: First is INSERT of what is not yet existing, second one is UPDATE on the appropriate attributes.

How would i write this sync procedure?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 16:52:14
There is a tool from RedGate that does this for you.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 2007-07-23 : 17:05:13
BUT I HAVE TO WRITE A SCRIPT THAT DOES THE ABOVE THINGS

SECONDLY WHAT DO I WRITE FOR TABLENAMES
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 2007-07-23 : 17:10:48
CREATE PROCEDURE CompareTables(@HPSTYPE varchar2(100), @WWGD_SERVICENOTES Varchar2(100), @NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar2(1000), @SERVICE_NAME, REBOOT_FLAG, BITS_AVAIL, RTP_LEVEL, ETF_FLAG, DATE_VENDORRELEASE, DATE_ACQUISITION, DATE_TEST, DATE_PRODUCTION, INDIVIDUAL_PRODUCTION, DATE_OBSOLETE, INDIVIDUAL_OBSOLETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE varchar2(1000) = '')

AS

declare @SQL varchar2(8000);

IF @WWGD_SERVICENOTES = '' SET @WWGD_SERVICENOTES = @HPSTYPE

set @SQL = 'SELECT ''' + @HPSTYPE + ''' AS TableName, ' + @NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar2(1000) + ' FROM ' + @HPSTYPE + ' UNION ALL SELECT ''' + @WWGD_SERVICENOTES + ''' As TableName, ' + @SERVICE_NAME, REBOOT_FLAG, BITS_AVAIL, RTP_LEVEL, ETF_FLAG, DATE_VENDORRELEASE, DATE_ACQUISITION, DATE_TEST, DATE_PRODUCTION, INDIVIDUAL_PRODUCTION, DATE_OBSOLETE, INDIVIDUAL_OBSOLETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE varchar2(1000) + ' FROM ' + @WWGD_SERVICENOTES

set @SQL = 'SELECT Max(TableName) as TableName, ' + @NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar2(1000) + ' FROM (' + @SQL + ') A GROUP BY ' + @NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar2(1000) + ' HAVING COUNT(*) = 1'

exec ( @SQL)


IN THE ABOVE WRITTEN SCRIPT WHAT WOULD BE MY SELECT STATEMENTS AND MY TABLENAMES

PLEASE ADVICE
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-07-24 : 03:33:59
"First is INSERT of what is not yet existing, second one is UPDATE on the appropriate attributes"

I would say First is to delete any rows no longer existing in Master table.

The deletes have to be done in "reverese FK order" and the inserts in "forward FK order". Even so the need for Updates may break the FKs - e.g. you want to delete a row referenced in an FK which WILL be fixed by the Update but that hasn't run yet.

We use a script to generate:

DELETE D
FROM DestDatabase.dbo.[MyTable] AS D
WHERE NOT EXISTS
(
SELECT *
FROM SourceDatabase.dbo.[MyTable] AS S
WHERE
D.[MyPK1] = S.[MyPK1]
AND D.[MyPK2] = S.[MyPK2]
)
...
IF EXISTS (SELECT * FROM SourceDatabase.dbo.[MyTable])
BEGIN
UPDATE D
SET
[MyCol1] = S.[MyCol1],
[MyCol2] = S.[MyCol2],
...
FROM DestDatabase.dbo.[MyTable] AS D
JOIN SourceDatabase.dbo.[MyTable] AS S
ON D.[MyPK1] = S.[MyPK1]
AND D.[MyPK2] = S.[MyPK2]
WHERE
(
(D.[MyCol1] <> S.[MyCol1]
OR (D.[MyCol1] IS NULL AND S.[MyCol1] IS NOT NULL)
OR (D.[MyCol1] IS NOT NULL AND S.[MyCol1] IS NULL))
OR (D.[MyVarcharCol] COLLATE Latin1_General_BIN <> S.[MyVarcharCol]
OR (D.[MyVarcharCol] IS NULL AND S.[MyVarcharCol] IS NOT NULL)
OR (D.[MyVarcharCol] IS NOT NULL AND S.[MyVarcharCol] IS NULL))
...
)

END
GO

IF EXISTS (SELECT * FROM SourceDatabase.dbo.[MyTable])
BEGIN
INSERT INTO DestDatabase.dbo.[MyTable]
(
[MyCol1],
[MyCol2],
...
)
SELECT S.*
FROM SourceDatabase.dbo.[MyTable] AS S
WHERE NOT EXISTS
(
SELECT *
FROM DestDatabase.dbo.[MyTable] AS D
WHERE
D.[MyPK1] = S.[MyPK1]
AND D.[MyPK2] = S.[MyPK2]
)
END

Kristen
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 2007-07-24 : 09:56:20
can some one tell me a store procedure script to update contents of one table from contents of another.

Help would be appreciated.

Thank you.
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 2007-07-24 : 10:31:57
I AM NOT A ORACLE GUY BUT NEED TO RUN THIS AS I RUN MY OWN BUSINESS. SO PLEASE HELP OUT.


CREATE PROCEDURE [dbo].[testINS]

(

@AValue int

)

AS

DECLARE @TType nvarchar(3)

SET @TType = 'INS'

BEGIN

INSERT INTO [dbo].[OriginalTable]([aValue]) VALUES (@AValue)

INSERT INTO [dbo].[TransactionsTable]([TType], [aValue]) VALUES (@TTYPE, @AValue)

END

GO

CREATE PROCEDURE [dbo].[testUPD]

(

@oldAValue int

@newAValue int

)

AS

DECLARE @TType nvarchar(3)

SET @TType = 'UPD'

BEGIN

UPDATE [dbo].[OriginalTable] SET [aValue] = @newAValue WHERE [aValue] = @oldAValue

INSERT INTO [dbo].[TransactionsTable]([TType], [aValue], [newAValue]) VALUES (@TTYPE, @oldAValue, @newAValue)

END

GO
-----------------------------------------------------------

i got the above code but the problem is I don't know what to enter where
if someone could help me.

The tables i need to feed are below.
basically table 2 columns have to compare and update from columns in table 1.

can you please help me write a procedure using the info below.


Table 1: MPTYPE

COLUMNS IN TABLE 1:

NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT


TABLE 2: WWW_SVNOTES

COLUMNS IN TABLE 2:

SERVICE_NAME, REBOOT_FLAG, BITS_AVAIL, RTP_LEVEL, ETF_FLAG, DATE_VENDORRELEASE, DATE_ACQUISITION, DATE_TEST, DATE_PRODUCTION, INDIVIDUAL_PRODUCTION, DATE_OBSOLETE, INDIVIDUAL_OBSOLETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 2007-07-25 : 12:11:25
I figured it out but if any one want to know how here you go:

PROCEDURE p_sync_sn
AS

BEGIN

MERGE INTO WWGD_SERVICENOTES B
USING (
SELECT SERVICE_ID, CATGROUP, DESCRIPT, REBOOT
FROM HPSTYPE
WHERE CATGROUP is NOT NULL) E
ON (E.SERVICE_ID = B.SERVICE_NAME)
WHEN MATCHED THEN
UPDATE
SET B.SVC_CATGROUP = E.CATGROUP,
B.SVC_DESCRIPT=E.DESCRIPT,
B.REBOOT_FLAG = E.REBOOT
WHEN NOT MATCHED THEN
INSERT (SERVICE_NAME, REBOOT_FLAG, SVC_CATGROUP, SVC_DESCRIPT)
VALUES (E.SERVICE_ID, E.REBOOT, E.CATGROUP, E.DESCRIPT);

commit;

exception
when others then
rollback;
raise;

end p_sync_sn;
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 2007-07-26 : 11:18:33
figured it out
Go to Top of Page

motormal
Starting Member

1 Post

Posted - 2011-12-13 : 16:09:36
As mentioned in other replies there are tools that can do this for you. If you're cheap like me you'll find free ones that work well. Like [url]http://www.w3enterprises.com/software/sql-table-compare.aspx[/url] which also has the source code available.
Go to Top of Page

henggi667
Starting Member

1 Post

Posted - 2014-08-20 : 10:53:39
This is what i basically was looking for... thanks Jeff!

Can i make this work if i have "text" field types included that need to be compared?
Server tells me to use LIKE instead of = to compare - don't know about the "UNION ALL"-clause...

thank you for any input!

quote:
Originally posted by jsmith8858

Based on the discussion at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23038

and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!

The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.


CREATE PROCEDURE CompareTables(@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS

-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)


- Jeff

Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-08-20 : 16:53:48
You can convert text to VARCHAR(MAX) and it will work:


DECLARE @t1 table (id int, t text);
DECLARE @t2 table (id int, t text);

insert @t1 values(1,replicate('abc',3000));
insert @t2 values(1,replicate('abc',3000));

select max(id) as ID, t
FROM
(
select id, CONVERT(varchar(max),t) as t from @t1
union all
select id, CONVERT(varchar(max),t) as t from @t2
) as z
GROUP BY t
HAVING COUNT(*) = 1


Unfortunately, you will have to modify the original SP to make this work. You can't pass in the CONVERT within the column list because you would need a column name and that conflicts with the group by. The simplest way to handle it would just be to add a new parameter to capture column names and use that one in the group by, however there are several options if you want to get fancy.
Go to Top of Page
   

- Advertisement -