Need some help with SQL

[bachphi]

I have 2 tables: TABLE1 with id, SerialNumber, PassFail, Data1, Data2, etc.....
                       TABLE2 with id, SerialNumber, PassFail.

As TABLE1 get updated by "insert" from app, I would like TABLE2 to be updated with the latest info.
Can this be done  with a SQL script or stored procedure automatically? 
TIA.

[Archie]

Will the same data be stored in both tables?

[bachphi]

Yes. Noted that Table2 has only 3 columns.

To update it manually, I can try something like:

Code: [Select]

UPDATE
     Table2
SET
     Table2.SerialNumber = Table1.SerialNumber,
     Table2.PassFail     = Table1.PassFail
FROM
     Table2
INNER JOIN     
     Table1
ON     
     Table2.id = Table1.id
WHERE
     Table2.SerialNumber != Table1.SerialNumber OR
     Table2.PassFail     != Table1.PassFail      OR
     (Table1.SerialNumber is not null and Table2.SerialNumber is null) OR
     (Table1.PassFail is not null and Table2.PassFail is null)
-- Maybe the last one is sufficient


I am not sure how to do it automatically? as new records get updated from Table1

[Archie]

Maybe you can create a Trigger in the DB as such:

Code: [Select]

CREATE Trigger MyTrigger
ON [Table1]
AFTER INSERT
AS
BEGIN
 INSERT INTO [Table]
 SELECT TOP 1 SerialNumber, PassFail
 FROM Table1
 ORDER BY ID DESC
END


[bachphi]

Wow, TRIGGER that  sounded like what I need.  I will try it tomorrow. Thanks Archie

[bachphi]

It works well!  a small modification:

Code: [Select]

BEGIN
 INSERT INTO [Table2]
 SELECT  SerialNumber, PassFail FROM INSERTED
 WHERE SerialNumber LIKE '_%_%P%'
END