June 30, 2010 at 11:54 pm
I have two tables which contain the same records but those records are entered by different users and i want to calculate similarity between same records(by comparing every single field) entered by different users, and if a field in one table is similar to corresponding field in the second table then i want to store 1 in a column in third table (Table_C).
For Example I have two tables Table_A and Table_B as below:
Table_A
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 123 CS 345
3 223 TE 190
Table_B
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 223 TE 345
3 223 TE 190
and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Structure of the table is as follows:
Table_C
----------------------
Sim_RecordID Sim_StudentID Sim_Dept Sim_BookID
1 1 1 1
1 1 1 1
Note: I want to compare only those records in Table_A and Table_B where Recorded and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field. Also i have a lot of such pairs of tables, so i want to write a dynaimc Sql query or stored procedure to get similar fields from Table_A and Table_B and create a new table Table_C dynamically with column names starting from Sim_[Table_A.column], that is it should dynamically give the column name to all the columns in Table_C. I hope i have clearly defined my probelm. any help would be appreciated.
July 1, 2010 at 1:02 am
Declare @vsql varchar(max)
Declare @vCols varchar(max)
Create Table vTable1 (id int, StudentID int, Dept varchar(10),BookID int)
Create Table vTable2 (id int, StudentID int, Dept varchar(10),BookID int)
Insert into vTable1
Select 1,123,'CS',465 Union All
Select 2,123,'CS',345 Union All
Select 3,223,'TE',190
Insert into vTable2
Select 1,123,'CS',465 Union All
Select 2,223,'TE',345 Union All
Select 3,223,'TE',190
-- Get the column names from schema with case statements to get 0 or 1 as result
-- Now, this will depend upon the columns of your actual tables
Select @vCols = Stuff((Select ',case when a.' + [name] + ' = b.' + [name] + ' then 1 else 0 end ' from sys.columns where Object_id = Object_id('vTable1') for XML Path('')),1,1,'')
Select @vCols
-- Concatenate the @vCols with main sql
Set @vsql = ' Select ' + @vCols + ' From vTable1 a
Inner Join vTable2 b on b.ID = a.ID '
Print @vsql
Exec (@vSQL)
Drop table vTable1
Drop table vTable2
You can re-write and convert the above specified code into SP. The SP will receive 3 parameters.
Parameter 1: Table 1
Parameter 2: Table 2
Parameter 3: Joining condition for table1 and table2
Last, when you are calling the new created sp, you will have the schema information of the table1 and table2 (that are schema wise identical) with you. You can create temporary table (Table_C) and populate by
Insert into Table_C
exec NewCreatedSP (@p1,@p2,@p3)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply