Avatar of Rama Tito
Rama Tito
Flag for Malaysia asked on

Data segreation in MSSQL 2005

Hi, I have a table name as "MasterInComingData", where i receive all kind of data from my machinery, Table design as follows:-
Column Name      DataType

No                             int                       (primary key)
MachineNo            int
MacLoc                   varchar(50)
TimeRec                 varchar(50)
Temp                       varchar(50)
Alarm                      varchar(50)

My questions is, how to segregate in programmability in MSSQL 2005. Upon data receive in "MasterInComingData". I wanted to segregate the data into a table "Alarm" and "Temp".
Where "Alarm" table get data from "MasterInComingData" only the MachineNo, TimeRec and Alarm.
For Temp table get data from "MasterInComingData" only the MachineNo, TimeRec and Temp.
how to do in program method in MSSQL 2005.
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
sachinpatil10d

8/22/2022 - Mon
sachinpatil10d

Use Trigger on MasterInComingData table to achieve your task.

Run the below script and insert data into MasterInComingData table.

-- =============================================
-- Author:		sachin
-- Create date: 25-05-2012
-- Description:	Insert values in two different tables
-- =============================================
CREATE TRIGGER dbo.t_InsData 
   ON  dbo.MasterInComingData 
   AFTER INSERT
AS 
BEGIN
	Insert into Alarm(MachineNo, TimeRec, Alarm)
	select I.MachineNo, I.TimeRec, I.Alarm from inserted I

	Insert into Temp(MachineNo, TimeRec, Temp)
	select I.MachineNo, I.TimeRec, I.Temp from inserted I

END
GO

Open in new window

Rama Tito

ASKER
Hi, My incoming message format in format . - > [machineno, macloc, time, temperature, alarm].

By default: either one of parameter in temperature or alarm will be "null". i have to check condition to insert which table after data arrival. If temperature data is not equll to null then i have to insert in temp table.

I am very new for MSSQL. To insert the scrip do i have to do this:-
right click on "MasterInComingData" then select "Script table as" then "SELECT To" then "New Query Editor Window"


CREATE TRIGGER dbo.t_InsData  (is that we do create another table here)
ASKER CERTIFIED SOLUTION
sachinpatil10d

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rama Tito

ASKER
Thank you, I do have another question which will posted shortly
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
sachinpatil10d

Welcome...