Data segreation in MSSQL 2005

Rama Tito
Rama Tito used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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 TitoProgrammer

Author

Commented:
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)
Try this

open sql server managment studio>>Login>>Click on New Query and paste the below query>>Select your databse>>Run the below script

-- =============================================
-- 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
	if (select I.Alarm from inserted I) is not null
	begin
		Insert into Alarm(MachineNo, TimeRec, Alarm)
		select I.MachineNo, I.TimeRec, I.Alarm from inserted I
	end 
	
	if (select I.Temp from inserted I) is not null
	begin
		Insert into Temp(MachineNo, TimeRec, Temp)
		select I.MachineNo, I.TimeRec, I.Temp from inserted I
	end 
END
GO

Open in new window

Rama TitoProgrammer

Author

Commented:
Thank you, I do have another question which will posted shortly
Welcome...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial