Production Data 2

Transact-SQL
1

I need to move data inserted from a holding table to individual tables based on a column in the holding data.
Eg. Holding table has a column CELL, which has values 1 thru 12.
There are 10 other tables CELL1 .. CELL12

The data in the holding table needs to be moved as \ after insertion into it. A trigger is a easy way, but how do I pass the parameters for specific table ?

Holding table
CREATE TABLE [dbo].[BANK1RAW](
[DTStamp] NOT NULL,
[PLCTime] NOT NULL,
[CellNumber] [int] NOT NULL,
[ReasonCode] [int] NOT NULL,
[Shift] [int] NOT NULL,
[CycleComplete] [bit] NOT NULL
) ON [PRIMARY]

CELL1
CREATE TABLE [dbo].[CELL1](
[DTStamp] NOT NULL,
[PLCTime] NOT NULL,
[ReasonCode] [int] NOT NULL,
[Shift] [int] NOT NULL,
[CycleComplete] [bit] NOT NULL
) ON [PRIMARY]

CELL2
CREATE TABLE [dbo].[CELL2](
[DTStamp] NOT NULL,
[PLCTime] NOT NULL,
[ReasonCode] [int] NOT NULL,
[Shift] [int] NOT NULL,
[CycleComplete] [bit] NOT NULL
) ON [PRIMARY]

SAMPLE DATA -
Insert into BANK1RAW Values (GetDate(), '2018-09-27 10:53:17.853', '1', '10','1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 10:53:27.853', '2', '10','1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 10:54:17.853', '2', '20','1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 10:54:18.853', '2', '30','1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 10:55:19.853', '1', '20','1', '1')

The idea is to move rows into its cell table as its inserted into the main table. Condition is CELL = 1 or CELL = 2. This example uses 2 cells, but I have 24 cells in the real application.

Thanks,

Mohit.

2

how does the holding table get populated (multiple sources at the same time or linear or multiple sources based on Cell)? Whatever process loads the table, can then call a stored procedure and pass the Cell # to it and act on that accordingly. If one process that loads all the data and multiple cells, then after it the table is loaded, a stored proc can be called to get all Distinct Cells and process one at a time

3

We have a aggregator PLC [Programmable Logic Controller], which aggregates all the data from multiple PLCs [12]. Then it moves to a database .. the holding table. I could write to specific tables, but need a holding table as well. For audit purposes. Can you show some code. I am not a IT person, and this is pilot project.

Thanks

Mohit.

4

An insert trigger should do the trick.


CREATE TRIGGER TRInsertBANK1RAW ON dbo.BANK1RAW
AFTER INSERT
AS
INSERT INTO dbo.CELL1 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =1;
INSERT INTO dbo.CELL2 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =2;
INSERT INTO dbo.CELL3 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =3;
INSERT INTO dbo.CELL4 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =4;
INSERT INTO dbo.CELL5 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =5;
INSERT INTO dbo.CELL6 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =6;
INSERT INTO dbo.CELL7 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =7;
INSERT INTO dbo.CELL8 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =8;
INSERT INTO dbo.CELL9 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =9;
INSERT INTO dbo.CELL10 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =10;
INSERT INTO dbo.CELL10 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =10;
INSERT INTO dbo.CELL12 ([DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete])
SELECT [DTStamp],[PLCTime],[ReasonCode],[Shift],[CycleComplete]
FROM Inserted
WHERE Inserted.CellNumber =12;
GO
5

Joseph,

That was simply awesome. Didn't even think of it. It worked right out of the box.

Thanks,

Mohit.