Data Comparison between two table (SQL Developer)

This Procedure is comparing two table values, Specially useful for QA having Back End Testing job, We can import any .txt or excel file into our local database and then we can compare it with any other Database's Table. 

Procedure is taking 6 Parameters in which 1 is optional , 

@first_db = <first database name / Source DB>, 

@first_table = <fist DB's Table name>

@first_col_list = <column list of first table which need to be display>

@second_db = <second database name /Second DB>

@second_table = <second DB's Table name>

@second_col_list = <column list of second table could be ' ' in case of same values>

This code is displaying all common rows in both table and most important thing is , Procedure is making its own matching criteria by capturing all Primary Keys of Second Database which is source Database or you can say Production Database. and only Matching Rows will be displayed.

Hold on guys.............  the most important part of the Procedure is still remaining , you can get the query generated behind the scene and can modify that query as per your requirement. just clink of the tab Messages and you will get the Dynamic Query behind the scene, 

Just copy the query from message window and play, For Example you can get the rows which are not matching but just changing WHERE EXISTS clause with  WHERE NOT EXISTS

Alright Guys See you next time with something different

Procedure is (actual code)

CREATE PROCEDURE [dbo].[GetComparison_SECOND] (@first_db varchar(100), @first_table varchar(100), @first_col_list varchar(1000),
                                              @second_db varchar(100), @second_table varchar(100), @second_col_list varchar(1000))
as
declare
@sql1 varchar(3000),
@sql2 varchar(3000),
@sql3 varchar(3000),
@sql_pk varchar(3000),
@col_name varchar(100),
@counter int,
@data_type varchar(50);
declare @key_cur as cursor;
SET @sql1 = ''
SET @sql2 = ''
set @sql_pk = ''


if @second_col_list = '' SET @second_col_list = @first_col_list
IF OBJECT_ID('tempdb..#KeysTemp') IS NOT NULL
        DROP TABLE #KeysTemp;
BEGIN
SET @sql_pk = 'select KCU.COLUMN_name COL_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH CHAR_LENGTH, c.NUMERIC_PRECISION NUMERIC_PRECISION,
              c.NUMERIC_PRECISION_RADIX NUMERIC_PREC_RADIX
              from '+@second_db
              +'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
              Join '+@second_db+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
              ON  KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
              AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
              AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
              AND KCU.TABLE_NAME = TC.TABLE_NAME
              join '+@second_db+'.INFORMATION_SCHEMA.COLUMNS C
              ON c.TABLE_CATALOG = tc.TABLE_CATALOG
              and c.TABLE_SCHEMA = tc.TABLE_SCHEMA
              and c.TABLE_NAME = tc.TABLE_NAME
              and c.COLUMN_NAME = kcu.COLUMN_NAME
              and TC.TABLE_CATALOG = '''+@second_db+''' and TC.TABLE_NAME = '''+@second_table+''''
CREATE TABLE #KeysTemp
(
 COL_NAME VARCHAR(100),
 DATA_TYPE VARCHAR(50),
 CHAR_LENGTH INT,
 NUMERIC_PRECISION INT,
 NUMERIC_PREC_RADIX INT
 )   
Insert
into #KeysTemp
exec (@sql_pk)

SET @sql1 = 'SELECT '+@first_col_list++CHAR(10)+' FROM '+@first_db+'.dbo.'+@first_table+ ' as COPY'+CHAR(10)+
           ' WHERE EXISTS'+ CHAR(10)+
           '(SELECT '+@second_col_list++CHAR(10)+' FROM '+@second_db+'.dbo.'+@second_table +' as ORIG'+ CHAR(10)+
           ' WHERE '+ CHAR(10)

SET @key_cur = CURSOR FOR
                SELECT COL_NAME, DATA_TYPE
                from #KeysTemp ;
set @counter = 0
    OPEN @key_cur
FETCH NEXT FROM @key_cur into @col_name, @data_type;
WHILE @@FETCH_STATUS = 0
BEGIN
     select @counter = @counter + 1
     IF @counter = 1
 IF @data_type = 'varchar'
BEGIN
SET @sql2 = 'ORIG.'+@col_name +' = ISNULL(COPY.'+@col_name+' ,0)'
END
ELSE IF @data_type = 'int'
BEGIN
SET @sql2 = 'ORIG.'+@col_name +' = ISNULL(COPY.'+@col_name+',0)'
END
ELSE IF @data_type = 'datetime'
BEGIN
SET @sql2 = 'ORIG.'+@col_name +' = ISNULL(COPY.'+@col_name+',0)'
END
ELSE IF @data_type = 'float'
BEGIN
SET @sql2 = 'ORIG.'+@col_name +' = ISNULL(CAST(LTRIM(RTRIM(COPY.'+@col_name+')) as VARCHAR(10)),0)'
END
ELSE SET @sql2 = 'ORIG.'+@col_name +'= COPY.'+@col_name
   
     ELSE
 
     IF @data_type = 'varchar'
BEGIN
SET @sql2 = @sql2 + CHAR(10)+' and ORIG.'+@col_name +' = ISNULL(COPY.'+@col_name+',0)'
END
ELSE IF @data_type = 'int'
BEGIN
SET @sql2 = @sql2 + CHAR(10)+' and ORIG.'+@col_name +' = ISNULL(COPY.'+@col_name+',0)'
END
ELSE IF @data_type = 'datetime'
BEGIN
SET @sql2 = @sql2 + CHAR(10)+' and ORIG.'+@col_name +' = ISNULL(COPY.'+@col_name+',0)'
END
ELSE IF @data_type = 'float'
BEGIN
SET @sql2 = @sql2 + CHAR(10)+' and ORIG.'+@col_name +' = ISNULL(CAST(LTRIM(RTRIM(COPY.'+@col_name+')) as VARCHAR(10)),0)'
END

ELSE SET @sql2 = @sql2 + CHAR(10)+' and ORIG.'+@col_name +' = COPY.'+@col_name

 
     FETCH NEXT FROM @key_cur into @col_name, @data_type;
END
CLOSE @key_cur;
DEALLOCATE @key_cur;

SET @sql3 = ''  

       
SET @sql3 = @sql1 + @sql2++CHAR(10)+')'       

print @sql3

exec (@sql3)
END

Other Developer related blogs
analytical functions
sqlcmd export data to txt
generate current foreign keys script
getting LEAD and LAG values manually
table data comparison