[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

urgently need dynamic SQL to compare corresponding columns data content

question to ScottPletcher, et al - in a previously posted question "Title: Temp tables compare" (From: kumaranCN), scott replied:

>>If you wanted to, you could generate dynamic SQL to compare corresponding columns, that is, the first column on table1 to the first column on table1, the second column on table1 to the second column on table2, etc..  If you interested in that, please post a message stating that and I will code it up and post it.  

I am interested in a dynamic column-wise comparison sql that will compare content of corresponding columns between 2 similarly metadata-structured tables

Any chance if you could please code up and post such an example?

Much thanks!

0
gregAppDev
Asked:
gregAppDev
1 Solution
 
Scott PletcherSenior DBACommented:
I will get to work on it, but it will probably take an hour or so.
0
 
Scott PletcherSenior DBACommented:
Are you in a rush for a specific table to be compared and do you just need a routine in general?

If specific, how do the rows from the tables relate, that is, is there a primary key that is matched on each table or some other column(s)?
0
 
Scott PletcherSenior DBACommented:
The code below is not nearly fully ready, but it may help give you some ideas or may even do what you need to do.  Take the results, copy them to QA input window, verify the join clause (ON ...), remove the excess "AND" at the end, and see if the code is close to what you need.


DECLARE @table1 VARCHAR(40)
DECLARE @table2 VARCHAR(40)
DECLARE @join VARCHAR(200)
SET @table1 = 'tableName1'
SET @table2 = 'tableName2'
SET @join = '<code join condition here>'


SET NOCOUNT ON
IF OBJECT_ID('tempdb..#columns') IS NOT NULL
     DROP TABLE #columns
CREATE TABLE #columns (
     tableName VARCHAR(40),
     columnName VARCHAR(50),
     datatype VARCHAR(50),
     columnId SMALLINT,
     ident BIT,
     prec SMALLINT,
     scale INT,
     isComputed BIT,
     isNullable BIT
)

INSERT INTO #columns
SELECT OBJECT_NAME(sc.id), sc.name, st.name, sc.colid,
     CASE WHEN sc.status & 80 > 0 THEN 1 ELSE 0 END,
     sc.prec, sc.scale, sc.isComputed, sc.isNullable    
FROM syscolumns sc
INNER JOIN systypes st ON sc.xtype = st.xtype
WHERE id IN (OBJECT_ID(@table1), OBJECT_ID(@table2))

PRINT 'SELECT t1.*, CHAR(13), t2.* '
PRINT 'FROM ' + @table1 + ' t1 '
PRINT 'INNER JOIN ' + @table2 + ' t2 ON ' + @join
PRINT 'WHERE '

SELECT 't1.' + c1.columnName + ' <> t2.' + c2.columnName + ' AND ' + CHAR(13)
FROM #columns c1
INNER JOIN #columns c2 ON c1.columnName = c2.columnName AND c2.tableName = @table2
WHERE c1.tableName = @table1


0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
gregAppDevAuthor Commented:
thanks scott for your input.

np. I can hold for some time.

specificially the 2 tables have composite keys, and I wanted to compare both the pk and non-pk fields where tbl1.column <> tbl2.column but had some difficulty envisioning this.



0
 
smurffCommented:
listening
0
 
CleanupPingCommented:
gregAppDev:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now