Author Topic: Need some help with SQL  (Read 850 times)

bachphi

  • Hero Member
  • *****
  • Posts: 642
    • View Profile
Need some help with SQL
« 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.
===================================================
This is NOT alt.read.my.mind.
No such thing is sh^t-for-brains unless you are posting to alt.read.my.mind.
===================================================

Archie

  • Administrator
  • Hero Member
  • *****
  • Posts: 5260
    • View Profile
    • AdvancedHMI
Re: Need some help with SQL
« Reply #1 on: April 03, 2017, 07:14:42 PM »
Will the same data be stored in both tables?

bachphi

  • Hero Member
  • *****
  • Posts: 642
    • View Profile
Re: Need some help with SQL
« Reply #2 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
« Last Edit: April 03, 2017, 08:28:32 PM by bachphi »
===================================================
This is NOT alt.read.my.mind.
No such thing is sh^t-for-brains unless you are posting to alt.read.my.mind.
===================================================

Archie

  • Administrator
  • Hero Member
  • *****
  • Posts: 5260
    • View Profile
    • AdvancedHMI
Re: Need some help with SQL
« Reply #3 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

bachphi

  • Hero Member
  • *****
  • Posts: 642
    • View Profile
Re: Need some help with SQL
« Reply #4 on: April 03, 2017, 08:16:56 PM »
Wow, TRIGGER that  sounded like what I need.  I will try it tomorrow. Thanks Archie
===================================================
This is NOT alt.read.my.mind.
No such thing is sh^t-for-brains unless you are posting to alt.read.my.mind.
===================================================

bachphi

  • Hero Member
  • *****
  • Posts: 642
    • View Profile
Re: Need some help with SQL
« Reply #5 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
===================================================
This is NOT alt.read.my.mind.
No such thing is sh^t-for-brains unless you are posting to alt.read.my.mind.
===================================================