=Explanation of SQL=

This SQL can be used to to find out all the differences between two database rows. These may be

• The same row in two tables
• Two rows in the same table.

This query is helpful to compare a backup with the current table, or to compare two rows in the same table.

=Risk of Data Corruption if Run Improperly=

None. This is a selection query and no data is modified in the running of it.

=Notes=

• All values are Varchar(100).
• Numbers are written in scientific notation, and to get them back to a numeric format you MUST cast them to a float first (and not a different numeric type).

For example:

Convert( Decimal(18,4), Convert( Float, Value1 ) ) is fine, but

Convert( Decimal(18,4), Value1 ) will only give you the mantissa and not the correct value.

* Only values that changed are listed. Values that remained the same are not shown.

• A NULL value can indicate the value was NULL or the record/field did not exist.

=SQL=

code

– this query compares rows in two tables and lists all the rows that are different.

– Set these values

declare @ID1 int = (select ID from Account where CompanyName like 'Grand Image Inc%' and IsClient = 1);

declare @Table1 varchar(100) = '[2016-03-09 AccountUserField]';

declare @ID2 int = @ID1;

declare @Table2 varchar(100) = '[AccountUserField]';

– Now run – the rest is good!

declare @xml1 xml;

declare @xml2 xml;

declare @t table (

	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

	ColumnName varchar(100), 

	Value1 varchar(100), 

	Value2 varchar(100)

	);

declare @q nvarchar(2048) =

'	set @xmloutput = (

				select TOP 1 * from '+@Table1+' as [TABLE] where ID = '+convert(varchar(10), @ID1)+' Order by ID for XML AUTO, ELEMENTS 

			);'

EXECUTE sp_executeSql @q, N'@xmloutput XML OUTPUT', @xmloutput=@xml1 OUTPUT;

SET @q =

'	set @xmloutput = (

				select TOP 1 * from '+@Table2+' as [TABLE] where ID = '+convert(varchar(10), @ID2)+' Order by ID for XML AUTO, ELEMENTS 

			);'

EXECUTE sp_executeSql @q, N'@xmloutput XML OUTPUT', @xmloutput=@xml2 OUTPUT;

– now join the two XMLs using the ColumName and exclude any that are not different

with

XML1 as

(

	select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,

			T.N.value('.', 'nvarchar(100)') as Value

	from @xml1.nodes('/TABLE/*') as T(N)

),

XML2 as

(

	select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,

			T.N.value('.', 'nvarchar(100)') as Value

	from @xml2.nodes('/TABLE/*') as T(N)

)

insert into @t (ColumnName, Value1, Value2)

	select coalesce(XML1.NodeName, XML2.NodeName) as NodeName, 

			XML1.Value as Value1, 

			XML2.Value as Value2

	from XML1

		full outer join XML2

		on XML1.NodeName = XML2.NodeName

	where coalesce(XML1.Value, '')  coalesce(XML2.Value, '')        

	    -- and XML1.NodeName not in ('StoreID', 'ModifiedByUser', 'ModifiedByComputer', 'SeqID')

;

– Now show the changes

select T.ColumnName, T.Value1, T.Value2

from @t T

code

=Version Information=

• Entered : 4/2016
• Version : SQL 2008 or Higher

=Related SQLs=

• Backlinks include_pagepage_componentbacklinks