ID  First_Name       Last_name               Birthdate
1   Shradha          Deshkmukh               1981-12-25 00:00:00
2   Shradha          Verma                   1981-05-11 00:00:00
3   Divya            Dutta                   1982-07-21 00:00:00
4   Matthew          Palmer                  1983-12-28 00:00:00

 id fn      ln      dob
 1  Shradha Tiwari  1981-12-25 00:00:00
 2  Divya   Dutta   1983-07-21 00:00:00
 3  Sulabh  Manesar 1975-09-11 00:00:00
 4  Matthew Palmer  1983-12-28 00:00:00
 5  Samuel  Maxwell 1984-05-22 00:00:00

USE Testing
GO

IF OBJECT_ID('TEMPDB..#TMP_COMPARE') is not null
BEGIN
DROP TABLE #TMP_COMPARE
END 

CREATE TABLE #TMP_COMPARE(
FN varchar(20),
LN varchar(20),
dob smalldatetime,
PLN int default 0,
Pdob int default 0,
 Pmatch int default 0)

BEGIN
INSERT INTO #TMP_COMPARE
SELECT a.fn, a.ln, a.dob, 
case when a.ln = d.Last_name AND a.dob = d.Birthdate
        THEN 1          
        END AS #TMP_COMPARE.PMATCH,--Error in this line ..COMPARE.PM..  
 CASE WHEN a.dob <> d.Birthdate
        THEN 0  
         WHEN a.dob = d.Birthdate then 1 
        END AS #TMP_COMPARE.Pdob,           

 CASE WHEN a.ln <> d.Last_name 
        THEN 0
          WHEN a.ln = d.Last_name            
    then 1          
 END AS #TMP_COMPARE.PLN

FROM dbo.a  
 JoIN  d ON a.fn = d.First_Name     
END 

SELECT * FROM #TMP_COMPARE

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '.'

...

INSERT INTO #TMP_COMPARE
SELECT a.fn, a.ln, a.dob, 
case when a.ln = d.Last_name AND a.dob = d.Birthdate
        THEN 1          
        END AS PMATCH,--Error in this line ..COMPARE.PM..  
 CASE WHEN a.dob <> d.Birthdate
        THEN 0  
         WHEN a.dob = d.Birthdate then 1 
        END AS Pdob,           

 CASE WHEN a.ln <> d.Last_name 
        THEN 0
          WHEN a.ln = d.Last_name            
    then 1          
 END AS PLN

FROM dbo.a2 as a  
 JoIN  d ON a.fn = d.First_Name     
END 
...

quote:

---

Originally posted by dcs

Hi Vishakh. Many thanks for your response. I made the changes. It works. I just need to get the data from this table into another log table as per each first name.

Do you think this is the right approach since, as I mentioned in my original post, the original table is having 17 columns. And this table will be about a orders , item numbers, transaction number etc. This record will be scheduled to check as per a query and in case there is any change made on the side of a client it needs to fire a notification to the relevant authority.

Thanks in advance and again many thanks for ur suggestion.


DCS

---

    CREATE TABLE COMPARETABLE
    (BookNum varchar(20), ColumnNotMatch varchar(200) )

   INSERT INTO COMPARETABLE(BookNum,ColumnNotMatch)
   SELECT BookNum,
      CASE p1<>1 SET 'ItemCode,'+
      CASE p2<>1 SET 'Quantity,'+
        ....--I want this to work Like CONTINUE in C++ where it should --go and match all columns.

      CASE p17<>1 SET 'NumberOfBoxes'
    +'dont match'
    END
    FROM Table1 T1 JOIN TABLE2 AS T2   
    ON T1.BookNum= T2.BookNum
 

     SELECT 
     INSERT INTO LogTable 
             (BookingNum, 
              TimeOfCheck,
              ColumnsNotMatching)
   VALUES 
          SELECT BookNum,getdate(),@ColumnNotMatch FROM TABLE1 AS T         JOIN CompareTable CT
  ON T.BookNum=CT.BookNum