|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-05-16 : 08:27:43
|
| uma writes "I have a requirement in sql server 2000 to compare 2 records from same table by ignoring primary_key columns and display column names which are having different values( I have to compare 9 tables and if client add more columns to tables in next version this compare proc needs to take care of new fields also without changing procedure)for exampledrop table my_testgo create table my_test( testno int not null, test_name varchar(100) not null, effective_date datetime not null, val1 int null, val2 int null, val3 int null) alter table my_testadd constraint pk_my_test primary key(testno,test_name,effective_date) insert into my_testvalues(1,'my test case1','20040101',1,1,1)insert into my_testvalues(2,'my test case1','20040101',1,1,2)insert into my_testvalues(1,'my test case1','20040102',2,2,2)insert into my_testvalues(2,'my test case1','20040102',2,3,2)select * from my_test I want to compare these two records only val1,val2,val3 columns( by ignoring primary key columns )and wants to display 'there is difference in val3'select val1,val2,val3 from my_test where testno =1 and test_name = 'my test case1' and effective_date = '20040101'select val1,val2,val3 from my_test where testno =2 and test_name = 'my test case1' and effective_date = '20040101'-- val2 is different in below queries so I have to display val2 is differentselect val1,val2,val3 from my_test where testno =1 and test_name = 'my test case1' and effective_date = '20040102'select val1,val2,val3 from my_test where testno =2 and test_name = 'my test case1' and effective_date = '20040102'in procedure we are going to pass testno1testno2test_name effective_datetest_name,effective_date are same for two recordsThanks,Uma" |
|
comapring two rows from same table in sql server 2000