title: Inserting JSON file content with OPENJSON (SQL 2016) into a table, with one of columns not being part of JSON file
body: <p>It seems to be pretty straightforward to execute insert and store JSON in SQL table with OPENJSON as long as the content of Json matches table.</p>
  
  <p>The problem starts when one of the values I want to insert into a table is not part of the JSON string.</p>
  
  <p>I.E. In the scenario below, I am trying to pass date as a hardcoded variable @createdON, but I see following error:</p>
  
  <p>[Code: 207, SQL State: 42S22]  Invalid column name '20171128'.</p>
  
  <pre><code>'{
  DECLARE @json VARCHAR(MAX)
  DECLARE @createdON VARCHAR(MAX)
  
  SET @createdON = "20171128" 
  
  SET @json = '{
      "contactStateDescriptions": [
       {
         "ContactStateCategory": "Queue",
         "ContactStateDescription": "Ok",
         "ContactStateId": 1
       },
       {
         "ContactStateCategory": "PreQueue",
         "ContactStateDescription": "Wait",
         "ContactStateId": 2
       }
     ]
   }'
  INSERT INTO ContactState
  (
  ContactStateCategory, ContactStateDescription, ContactStateId, "DATE"
  )
  SELECT *, @CreatedON AS createdON
  FROM OPENJSON(@json, '$.contactStateDescriptions')
  WITH (
  ContactStateCategory NVARCHAR(255) '$.ContactStateCategory',
  ContactStateDescription NVARCHAR(255) '$.ContactStateDescription',
  ContactStateId NVARCHAR(255) '$.ContactStateId' );   
  }'
  </code></pre>
  
  <p>Basically, I am trying to enrich import of my JSON file with a date value that has not been included in JSON. </p>
  
  <p>BTW Replacing;</p>
  
  <p>DECLARE @createdON VARCHAR(MAX) and SET createdON = "20171128" </p>
  
  <p>with</p>
  
  <p>DECLARE @createdON DATETIME and SELECT createdON = GETDATE() </p>
  
  <p>makes this a correct query. It would be great to know why? </p>