urgently need dynamic SQL to compare corresponding columns data content

Posted on 2003-03-19
Medium Priority
Last Modified: 2008-02-01
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!

Question by:gregAppDev
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8168668
I will get to work on it, but it will probably take an hour or so.
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8168752
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)?
LVL 69

Accepted Solution

Scott Pletcher earned 40 total points
ID: 8169148
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.

SET @table1 = 'tableName1'
SET @table2 = 'tableName2'
SET @join = '<code join condition here>'

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

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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!


Author Comment

ID: 8169446
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.


Expert Comment

ID: 8172564

Expert Comment

ID: 9275723
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 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question