0

This query was from a 2017 post on this site and the results fit my requirements but I need them to a temp table. It currently writes to the screen with the column headers from each table and the values in each. When I try and insert it onto a temp table I can't get the column names, only the values. Basically I'm comparing two tables and need to report the differences in them. Your assistance is greatly appreciated.

DECLARE @ColName varchar(100)
DECLARE @Table1 varchar(100) = 'MyTable'
DECLARE @Table2 varchar(100) = 'MyOtherTable'


IF (OBJECT_ID('tempdb..#col') IS NOT NULL) DROP TABLE #col
SELECT  IDENTITY(INT, 1, 1) RowNum , c.name
INTO    #col
FROM    SYS.Objects o 
    JOIN SYS.columns c on o.object_id = c.object_id
WHERE   o.name = @Table1 AND NOT c.Name IN ('List','Columns','YouWantToIgnore')

DECLARE @Counter INT = (SELECT MAX(RowNum) FROM #col)

WHILE @Counter > 0

    BEGIN
        SET @ColName = (SELECT name FROM #Col WHERE RowNum= @Counter)
            EXEC ('SELECT  t1.Identifier
                    ,t1.'+@ColName+' AS '+@Table1+@ColName+'
                    ,t2.'+@ColName+' AS '+@Table2+@ColName+'
            FROM    '+@Table1+' t1
                    LEFT JOIN '+@Table2+' t2 ON t1.Identifier = t2.Identifier 
            WHERE   t1.'+@ColName+' <> t2.'+@ColName)
        SET @Counter = @Counter - 1 
    END
Alan Carita
  • 1
  • before exec, you could use PRINT to check if the query is what you needed. if you don't mind, post the print query and would help a lot, since we don't have those tables. – Jiacheng Gao Jan 28 '21 at 19:24
  • Which dbms are you using? (That code is very product specific.) – jarlh Jan 28 '21 at 19:54
  • To see how this works create 2 simple identical MS SQL tables. – Alan Carita Jan 29 '21 at 20:47
  • To see how this works create 2 simple identical MS SQL tables. Insert the same "Name" value in each table, then different values in each for another value. run my original query against these 2 table and it will show the column that has differences and the values from each. I need this output into a temp table that includes the table name, column names, and the values where they didn't match. – Alan Carita Jan 29 '21 at 20:52
  • CREATE TABLE [dbo].[MyTable]( [Name] [nvarchar](256) NULL, [SameData] [varchar](20) NULL, [DifferentData] [varchar](20) NULL, ) ON [PRIMARY]; CREATE TABLE [dbo].[MyOtherTable]( [Name] [nvarchar](256) NULL, [SameData] [varchar](20) NULL, [DifferentData] [varchar](20) NULL, ) ON [PRIMARY]; INSERT INTO [dbo].[MyTable] ([Name],[SameData],[DifferentData]) VALUES ('Name1','DataTest','valueMonday'); INSERT INTO [dbo].[MyOtherTable] ([Name],[SameData],[DifferentData]) VALUES ('Name1','DataTest','valueTuesday'); – Alan Carita Jan 29 '21 at 20:57

0 Answers0