SQL Server trigger to insert multiple rows with different row-set

Question

How to convert table with time series rows to column-based using SQL Server trigger?

Timeseries source table variable_table:

---------------------------------------------------
timestamp    var             property        value
---------------------------------------------------
1506598200   A01_NetOrder    49              10.0
1506598200   A01_NetOrder    50               3.5
1506598200   A01_NetRec      49              20.0
1506598200   A01_NetRec      50               4.0
--------------------------------------------------

Multiple rows will be inserted with trigger in destination table.

Destination table var_column_table:

---------------------------------------------------------
timestamp      loc       property    NetOrder     NetRec
---------------------------------------------------------
1506598200    A01        49          10.0         20.0
1506598200    A01        50           3.5          4.0
---------------------------------------------------------

Trigger work around

ALTER trigger [dbo].[rowColInsert]
ON [dbo].[variable_table]
AFTER INSERT 
AS
BEGIN
    SET NOCOUNT ON;

    declare @timestamp int
    declare @val varchar(125)
    declare @var varchar(125)
    declare @loc varchar(125)
    declare @col varchar(125)
    declare @cal int
    declare @sql varchar(max)

    select @timestamp = (select timestamp from inserted)
    select @var = (select var from inserted)
    select @property = (select property from inserted)
    select @value = (select value from inserted)

    select 

    select 
        @loc = (select left(@var, (select charindex('_', @var,0))-1))

    if exists (select timestamp from var_column_table where timestamp = @time)
    begin
        select @sql = 'update var_column_table set ' + @col + '=' + cast(@value as varchar)
        exec(@sql)
    end
    else
    begin
        select @sql = 'insert into var_column_table (timestamp, loc, property' + @col + ') values (' + cast(@timestamp as varchar) + ',' + cast(@loc as varchar) + ',' + cast(@property as varchar) + ','+ cast(@value as varchar) + ')'
        exec(@sql)
    end
 end

Please advise the work around to get the desired result. Note: inserted table has multiple records.

Edit

1) The var in variable_table has multiple prefix like: V01, V02, V03...

PacoDePaco · Accepted Answer · 2017-09-28 13:12:05Z

1

You need to access the inserted table in a merge statement. Your trigger:

     ALTER trigger [dbo].[rowColInsert]
        ON [dbo].[variable_table]
        AFTER INSERT 
        AS
        BEGIN
            SET NOCOUNT ON;

           MERGE var_column_table AS TARGET 
    USING
    (
    SELECT timestamp
    ,substring(var,0,charindex('_',var)) as loc
    , i1.property
    ,i1.value as NetOrder
    ,i2.value as NetRec
    FROM inserted i1 
    inner join inserted i2 on i1.timestamp = i2.timestamp and i1.property=i2.property
WHERE i1.property like '%NetOrder%' AND i2.property like '%NetRec%'
    ) AS SOURCE (timestamp, loc, property, NetOrder, NetRec)
    ON TARGET.timestamp = SOURCE.timestamp AND TARGET.property = SOURCE.property
    WHEN MATCHED THEN UPDATE
    SET
    TARGET.NetOrder = SOURCE.NetOrder,
    TARGET.NetRec = SOURCE.NetRec 
    WHEN NOT MATCHED THEN INSERT (timestamp, loc, property, NetOrder, NetRec)
    VALUES (source.timestamp, source.loc, source.property, source.NetOrder, source.NetRec);
         end

Please test if it works. Basically the 'inserted' table contains all of the rows inserted when the trigger fires. You can use the merge statement to perform upsert logic.

answered Sep 28, 2017 at 13:12

PacoDePaco

6991 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

NKR Over a year ago

Working fine. What if, there is 105 variable in variable table. So that these variables will be presented in inner join and where clause?

NKR Over a year ago

I tested this on secondary table say "variable_table1" in place of inserted table, with procedure having your codes, and it's working fine, rows inserted into var_column_table but trigger is not inserting. Any guess why?

NKR Over a year ago

Also added, loc equality in where clause. i1.loc = i2.loc.

PacoDePaco Over a year ago

Please check if the trigger is fired at all. You can 'log' the execution by changing the trigger code to insert into variable_table1 select * from inserted. If you insert any rows into variable_table they should pop up in the variable_table1 as well

NKR Over a year ago

Trigger is firing. Confirmed with:select TRG.name, ETS.last_execution_time from sys.dm_exec_trigger_stats AS ETS INNER JOIN sys.triggers AS TRG ON ETS.object_id = TRG.object_id

|

Collectives™ on Stack Overflow

SQL Server trigger to insert multiple rows with different row-set

Edit

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related