Support Questions
Find answers, ask questions, and share your expertise

Issue While Converting Json to Insert SQL for the SQL Server DB

Issue While Converting Json to Insert SQL for the SQL Server DB

New Contributor

Hi,

Please help me in resolving an issue while performing an insert into SQL Server DB.

Issue : None of the fields in the record map to fields defined in the DBNAME.[SCHEMANAME].TABLENAME 

Root Cause Analysis : MS SQL server expects string values to be in single quotes and column names to be in either double quotes or no quotes.

INSERT INTO DBNAME.[SCHEMANAME].TABLENAME
(column1,column2,column3)
values ('val1',val2,'val3')

My Design :

convertJsonToSQL  - PutSQL

Input to ConvertJSONtoSQL is a {"column1" : "val1" ,"column2" : "val2", "column3" : "val3"}

ConvertJSONToSQL fails saying the error "None of the fields in the record map to fields defined in the DBNAME.[SCHEMANAME].TABLENAME ".

Options Tried :

Also as a workaround I tried using convertRecord instead of ConvertJSONToSQL, to convert Json to CSV and then use PutDatabaseRecord, its also failing with the same issue. 

 

Please help me in having the values in Json key-value replaced with single quotes and have these inserts function proper. 

 

Used replaceText to replace all the double quotes of a Json value Json's key-value, if this is the case, convertJSONToSQL is failing stating invalid JSON since JSON is expected to have "Key" and "Value" in double quotes.