April 8, 2010 at 8:45 pm
Hello,
I have 2 tables that I need to compare and find the differences.
Table1:
CREATE TABLE STANDARD
(STATUS TINYINT,
cmd TINYINT,
ins VARCHAR(255),
del VARCHAR(255),
upd VARCHAR(255))
INSERT INTO [STANDARD]
(
[STATUS],
cmd,
ins,
del,
upd
)
VALUES
(
'8',
'2',
'sql',
'sql',
'sql'
)
standard
status cmd ins del upd
8 2 sql sql sql
table 2: Compare
CREATE TABLE compare
(servername SYSNAME,
STATUS TINYINT,
cmd TINYINT,
ins VARCHAR(255),
del VARCHAR(255),
upd VARCHAR(255))
INSERT INTO [compare] ( servername,[STATUS],cmd,ins,del,upd)
VALUES ( 'test1','8','2','sql','sp_del','sql')
INSERT INTO [compare] ( servername,[STATUS],cmd,ins,del,upd)
VALUES ( 'test1','9','2','sql','sql_del','sql')
INSERT INTO [compare] ( servername,[STATUS],cmd,ins,del,upd)
VALUES ( 'test2','9','2','sql','sql','sql')
INSERT INTO [compare] ( servername,[STATUS],cmd,ins,del,upd)
VALUES ( 'test3','8','2','sql','sql','sql')
serverName status cmd ins del upd
test1 8 2 sqlsp_del sql
test1 9 2 sql sql_del sql
test2 9 2 sql sql sql
test3 8 2 sql sql sql
I want help for finding which columns are different from both tables.
e.g . when comparing standard with compare table,
the result :
server test1 is having del column different: value is sp_del
server test1 is having status and delete columns different: value is 9 and sql_del.
I have tried Except and not in, but it is giving me rows that are not matching, but I want column level comparison to point out columns that are not matching.
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
April 8, 2010 at 10:36 pm
If the situation is really as simple as your example, then you can check it with case statements... (Note: sorry, not on a SQL Server atm, so not tested)
DECLARE @status tinyint
--Other Fields Here
SELECT @status = Status
--Other Fields here
FROM Standard
SELECT CASE WHEN
Status <> @status THEN 'Status: ' +
ISNULL(CAST(Status as varchar(10)),'') ELSE '' END +
-- Case Statements for Other Fields
FROM
(SELECT * FROM Compare
EXCEPT
SELECT * FROM Replace) A
If it's not, let us know what differs.
April 11, 2010 at 4:10 pm
Garadin (4/8/2010)
If the situation is really as simple as your example, then you can check it with case statements... (Note: sorry, not on a SQL Server atm, so not tested)
DECLARE @status tinyint--Other Fields Here
SELECT @status = Status
--Other Fields here
FROM Standard
SELECT CASE WHEN
Status <> @status THEN 'Status: ' +
ISNULL(CAST(Status as varchar(10)),'') ELSE '' END +
-- Case Statements for Other Fields
FROM
(SELECT * FROM Compare
EXCEPT
SELECT * FROM Replace) A
If it's not, let us know what differs.
Hello,
Thanks for your help. But I am not getting results that I want.
This is the code that I am using :
DECLARE @status TINYINT,
@cmd TINYINT,
@ins VARCHAR(255),
@del VARCHAR(255),
@upd VARCHAR(255)
--Other Fields Here
SELECT @status = STATUS,
@cmd = cmd,
@ins = ins,
@del = del,
@upd = upd
--Other Fields here
FROM Standard
SELECT
CASE WHEN status <> @status THEN 'Status is not as per standard: ' + ISNULL(CAST(Status as varchar(10)),'') ELSE '' END + ''+
case when cmd <> @cmd THEN 'Cmd (command) is not as per standard: ' + ISNULL(CAST(cmd as varchar(10)),'') ELSE '' END + ' '+
case when ins <> @ins THEN 'insert is not as per standard: ' + ISNULL(CAST(ins as varchar(10)),'') ELSE '' END + ' '+
CASE WHEN del <> @del THEN 'delete is not as per standard: ' + ISNULL(CAST(del as varchar(10)),'') ELSE '' END + ' '+
case when upd <> @upd THEN 'upd (update) is not as per standard: ' + ISNULL(CAST(upd as varchar(10)),'') ELSE '' END + ' '
-- Case Statements for Other Fields
FROM
(SELECT STATUS, cmd, ins,del,upd FROM Compare
EXCEPT
SELECT STATUS, cmd, ins,del,upd FROM STANDARD) as A
Below is the output that I am getting:
delete is not as per standard: sp_del
Status is not as per standard: 9
Status is not as per standard: 9 delete is not as per standard: sql_del
I want to include server name as well in the output.
Also in the output, I noticed that the second result Status is not as per standard: 9
should be last. (If you look at my table ).
If I am using select * in the final Except statement, I am getting below error :
Msg 205, Level 16, State 1, Line 15
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
As this is for a comparision, I have to include other colums as well to point where the standards are violated.
Thanks for your help !
Cheers,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
April 11, 2010 at 8:28 pm
Garadin (4/8/2010)
If the situation is really as simple as your example, then you can check it with case statements... (Note: sorry, not on a SQL Server atm, so not tested)
DECLARE @status tinyint--Other Fields Here
SELECT @status = Status
--Other Fields here
FROM Standard
SELECT CASE WHEN
Status <> @status THEN 'Status: ' +
ISNULL(CAST(Status as varchar(10)),'') ELSE '' END +
-- Case Statements for Other Fields
FROM
(SELECT * FROM Compare
EXCEPT
SELECT * FROM Replace) A
If it's not, let us know what differs.
First thanks to Seth Phelabaum for help 🙂
Now I am able to get the required result that I want. I am posting if anyone who has same problem like me or is new for TSQL can be benifited.
DECLARE
@status TINYINT,
@cmd TINYINT,
@ins VARCHAR(255),
@del VARCHAR(255),
@upd VARCHAR(255)
SELECT
@status = STATUS,
@cmd = cmd,
@ins = ins,
@del = del,
@upd = upd
FROM STANDARD
SELECT
CASE WHEN status <> @status THEN 'On '+ a.servername + ' server Status is not as per standard: ' + ISNULL(CAST(Status as varchar(10)),'')+ ' ' ELSE '' END + ''+
case when cmd <> @cmd THEN 'On '+ a.servername + ' server Cmd (command) is not as per standard: ' + ISNULL(CAST(cmd as varchar(10)),'') ELSE '' END + ' '+
case when ins <> @ins THEN 'On '+ a.servername + ' server insert is not as per standard: ' + ISNULL(CAST(ins as varchar(10)),'') + ' ' ELSE '' END + ' '+
CASE WHEN del <> @del THEN 'On '+ a.servername + ' server delete is not as per standard: ' + ISNULL(CAST(del as varchar(10)),'')+ ' ' ELSE '' END + ' '+
case when upd <> @upd THEN 'On '+ a.servername + ' server upd (update) is not as per standard: ' + ISNULL(CAST(upd as varchar(10)),'')+ ' ' ELSE '' END + ' '
FROM
(SELECT servername,STATUS, cmd, ins,del,upd FROM Compare
EXCEPT
SELECT '' AS servername,STATUS, cmd, ins,del,upd FROM STANDARD) as A ORDER BY a.servername
I didnt know that I can put a ' ' and alias it with the required column name... thanks to Tushar for helping me.
Now I have learned something new.
Thanks again,
😀
- INSPIRE: It is amazing what you can accomplish if you do not care who gets the credit.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply