1

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.

The problem starts when one of the values I want to insert into a table is not part of the JSON string.

I.E. In the scenario below, I am trying to pass date as a hardcoded variable @createdON, but I see following error:

[Code: 207, SQL State: 42S22] Invalid column name '20171128'.

'{
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' );   
}'

Basically, I am trying to enrich import of my JSON file with a date value that has not been included in JSON.

BTW Replacing;

DECLARE @createdON VARCHAR(MAX) and SET createdON = "20171128"

with

DECLARE @createdON DATETIME and SELECT createdON = GETDATE()

makes this a correct query. It would be great to know why?

LuckyAshnar
  • 355
  • 2
  • 12
  • `@CreatedON AS @CreatedON` is not valid syntax, as `@CreatedON` is not a valid column name -- you probably mean `AS CreatedON`. Nor is `SET @CreatedON = "20171128"` legal T-SQL (double quotes only escape identifier names; single quotes are necessary for values). – Jeroen Mostert Nov 28 '17 at 12:01
  • thanks for pointing out, edited – LuckyAshnar Nov 28 '17 at 12:39

0 Answers0