CODEWITHSUNDEEP
sqlazureazure-stream-analytics
Sam
Sam

Reputation: 1327

Reuse the result from subquery in Azure Stream Analytics Query

I am using Azure Stream Analytics and I am facing some problem on query part. Here is my codes.

WITH subquery as (
    SELECT 
    messageId,
    deviceId,
    temperature, 
    humidity,
    EventProcessedUtcTime,
    DemoML(temperature, humidity) as result1
    from DemoInput
    )

SELECT
    messageId as messageId,
    deviceId as deviceId,
    temperature as temperature,
    humidity as humidity,
    EventProcessedUtcTime as EventProcessedUtcTime,
    result1.[Scored Labels] as result,
    result1.[Scored Probabilities] as resultProbability
INTO
    [DemoOutput]
FROM
    [subquery]


SELECT
    result1
INTO
    [c2d]
FROM
    [subquery]

DemoML is a function where it will return the result. I want to put result1 into two different outputs. But I only managed to put result1 into one output. How can I achieve that? I am totally new to SQL.

Upvotes: 0

Views: 970

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

You could use INSERT ... OUTPUT ... INTO ... SELECT to insert twice:

WITH subquery as (
    SELECT 
    messageId,
    deviceId,
    temperature, 
    humidity,
    EventProcessedUtcTime,
    DemoML(temperature, humidity) as result1
    from DemoInput
    )
INSERT INTO target_1(col1,...)
OUTPUT inserted.col1, ...
INTO target_2(col1, ..)
SELECT
    messageId as messageId,
    deviceId as deviceId,
    temperature as temperature,
    humidity as humidity,
    EventProcessedUtcTime as EventProcessedUtcTime,
    result1.[Scored Labels] as result,
    result1.[Scored Probabilities] as resultProbability
FROM  [subquery];

Simplified:

CREATE TABLE t1(i INT);
CREATE TABLE t2(i INT);
CREATE TABLE src(i INT);
INSERT INTO src(i) VALUES(10),(20);

INSERT INTO t1(i)                 -- target one
OUTPUT inserted.i
INTO t2(i)                        -- target two
SELECT i
FROM src;

DBFiddle Demo

Upvotes: 1

Related Questions