SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Comparing two tables or views easily
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/15/2003 :  21:46:17  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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 - 08/07/2003 :  21:37:37  Show Profile  Reply with Quote
Great Stuff jeff, exactly what I was looking for.


Thanks,
Kevin
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 07/23/2007 :  16:49:02  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 07/23/2007 :  16:52:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 07/23/2007 :  17:05:13  Show Profile  Reply with Quote
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 - 07/23/2007 :  17:10:48  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 07/24/2007 :  03:33:59  Show Profile  Reply with Quote
"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

Edited by - Kristen on 07/24/2007 03:34:53
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 07/24/2007 :  09:56:20  Show Profile  Reply with Quote
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 - 07/24/2007 :  10:31:57  Show Profile  Reply with Quote
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

Edited by - parikhm3 on 07/24/2007 10:40:03
Go to Top of Page

parikhm3
Starting Member

12 Posts

Posted - 07/25/2007 :  12:11:25  Show Profile  Reply with Quote
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 - 07/26/2007 :  11:18:33  Show Profile  Reply with Quote
figured it out

Edited by - parikhm3 on 07/26/2007 13:58:20
Go to Top of Page

motormal
Starting Member

Canada
1 Posts

Posted - 12/13/2011 :  16:09:36  Show Profile  Reply with Quote
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 http://www.w3enterprises.com/software/sql-table-compare.aspx which also has the source code available.
Go to Top of Page

henggi667
Starting Member

Switzerland
1 Posts

Posted - 08/20/2014 :  10:53:39  Show Profile  Reply with Quote
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

USA
343 Posts

Posted - 08/20/2014 :  16:53:48  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000