title: How to send data to only one Azure SQL DB Table from Azure Streaming Analytics?
body: <p><strong>Background</strong></p>
  
  <p>I have set up an IoT project using an Azure Event Hub and Azure Stream Analytics (ASA) based on tutorials from <a href="https://www.hackster.io/pjdecarlo/hands-on-lab-particle-photon-weather-station-in-azure-d89a03" rel="nofollow noreferrer">here</a> and <a href="https://www.linkedin.com/pulse/getting-started-azure-iot-services-stream-analytics-rob-tiffany" rel="nofollow noreferrer">here</a>.  JSON formatted messages are sent from a wifi enabled device to the event hub using webhooks, which are then fed through an ASA query and stored in one of three Azure SQL databases based on the input stream they came from.</p>
  
  <p>The device (Particle Photon) transmits 3 different messages with different payloads, for which there are 3 SQL tables defined for long term storage/analysis.  The next step includes real-time alerts, and visualization through Power BI.</p>
  
  <p>Here is a visual representation of the idea:
  <a href="https://i.stack.imgur.com/1P2Wu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1P2Wu.png" alt="IoT: Particle Photon + Azure"></a></p>
  
  <p><strong>The ASA Query</strong></p>
  
  <pre><code>SELECT
      ParticleId,
      TimePublished,
      PH,
      -- and other fields
  INTO TpEnvStateOutputToSQL
  FROM TpEnvStateInput
  
  
  SELECT
      ParticleId,
      TimePublished,
      EventCode,
      -- and other fields
  INTO TpEventsOutputToSQL
  FROM TpEventsInput
  
  
  SELECT
      ParticleId,
      TimePublished,
      FreshWater,
      -- and other fields
  INTO TpConsLevelOutputToSQL
  FROM TpConsLevelInput
  </code></pre>
  
  <p><strong>Problem:</strong> For every message received, the <em>data is pushed to all three tables</em> in the database, and not only the output specified in the query.  The table in which the data belongs gets populated with a new row as expected, while the two other tables get populated with NULLs for columns which no data existed for.</p>
  
  <p>From the <a href="https://azure.microsoft.com/en-us/documentation/articles/stream-analytics-stream-analytics-query-patterns/" rel="nofollow noreferrer">ASA Documentation</a> it was my understanding that the INTO keyword would direct the output to the specified sink.  But that does not seem to be the case, as the output from all three inputs get pushed to all sinks (all 3 SQL tables).</p>
  
  <p>The test script I wrote for the Particle Photon will send one of each type of message with hardcoded fields, in the order: EnvState, Event, ConsLevels, each 15 seconds apart, repeating.</p>
  
  <p>Here is an example of the output being sent to all tables, showing one column from each table:
  <a href="https://i.stack.imgur.com/HQnYs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HQnYs.png" alt="enter image description here"></a></p>
  
  <p>Which was generated using this query (in Visual Studio):</p>
  
  <pre><code>SELECT 
      t1.TimePublished as t1_t2_t3_TimePublished, 
      t1.ParticleId as t1_t2_t3_ParticleID, 
      t1.PH as t1_PH, 
      t2.EventCode as t2_EventCode, 
      t3.FreshWater as t3_FreshWater
  FROM dbo.EnvironmentState as t1, dbo.Event as t2, dbo.ConsumableLevel as t3
  WHERE t1.TimePublished = t2.TimePublished AND t2.TimePublished = t3.TimePublished
  </code></pre>
  
  <p>For an input event of type TpEnvStateInput where the key 'PH' would exist (and not keys 'EventCode' or 'FreshWater', which belong to TpEventInput and TpConsLevelInput, respectively), an entry into <strong>only</strong> the EnvironmentState table is desired.</p>
  
  <p><strong>Question:</strong>
  Is there a bug somewhere in the ASA query, or a misunderstanding on my part on how ASA should be used/setup?</p>
  
  <p>I was hoping I would not have to define three separate Stream Analytics containers, as they tend to be rather pricey.  After running through <a href="https://github.com/BretStateham/connectthedots/blob/master/HOLs/Azure/AzurePrep/README.md" rel="nofollow noreferrer">this tutorial</a>, and leaving 4 ASA containers running for one day, I used up nearly $5 in Azure credits.  At a projected $150/mo cost, there's just no way I could justify sticking with Azure.</p>