February 20, 2014 at 3:35 pm
I've table A with 100 columns and Table B with 100 columns. Both table A and B should be identical in terms of number of rows and the values inside it. But sometime there are differences and task is to identify the differences; which can be achieved by EXCEPT command. I also like to show on the first column as MisMatchColumns, which should list all the non-matching columns for that row. For e.g if col2, col3 and col4 are not matching, then it should show col2,col3,col4 as non-matching columns. Is that doable?
February 20, 2014 at 8:47 pm
Probably the easiest way would be to use a query of one of the system tables and see if you can match that way...
use MyDb;
go
DECLARE @TableName VARCHAR(25);
SET @TableName = 'Hospital';
Select table_catalog
, table_schema
, table_name
, column_Name
, ordinal_position
, data_type
, character_maximum_length
from information_schema.columns
where table_name = @TableName;
Then if you had to, you could use a cursor to loop over the columns and compare the two tables. Basically create some dynamic SQL to query for differences.
February 20, 2014 at 9:49 pm
One option is to use SQL Data compare. I know it is not coding your own solution and does come with a price tag. But there is a 14 day eval and it is well worth it for this kind of stuff.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 20, 2014 at 10:14 pm
Good thing, because I was just starting to think about it and try to figure out a solution (Good learning exercise, but if you have actual work to do, not so great!)...
February 20, 2014 at 10:20 pm
pietlinden (2/20/2014)
Good thing, because I was just starting to think about it and try to figure out a solution (Good learning exercise, but if you have actual work to do, not so great!)...
That's kinda my take on it. It might be fun, but if you don't have time for it, find a tool.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply