AdvancedHMI Software

General Category => Open Discussion => Topic started by: bachphi on April 03, 2017, 07:02:23 PM

Title: Need some help with SQL
Post by: bachphi on April 03, 2017, 07:02:23 PM

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.

Title: Re: Need some help with SQL
Post by: Archie on April 03, 2017, 07:14:42 PM

Will the same data be stored in both tables?

Title: Re: Need some help with SQL
Post by: bachphi on April 03, 2017, 07:24:44 PM

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

Title: Re: Need some help with SQL
Post by: Archie on April 03, 2017, 07:51:23 PM

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


Title: Re: Need some help with SQL
Post by: bachphi on April 03, 2017, 08:16:56 PM

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

Title: Re: Need some help with SQL
Post by: bachphi on April 04, 2017, 12:03:39 PM

It works well!  a small modification:

Code: [Select]

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