CODEWITHSUNDEEP
c#jsonsql-server-2016
Rapsoulis
Rapsoulis

Reputation: 232

Insert JSON into SQL Server 2016 using C# and OPENJSON

I'm trying to use the OPENJSON to insert data into my database writing code in C#.

I've managed to insert the desired JSON object, but I can do that only once - which is not the goal. I want to use this way to insert more than one JSON objects into the database and keep them there.

The SQL string I'm using is this:

@"SELECT BulkColumn INTO unparsed2 FROM OPENROWSET(BULK 'C:\\JSON\\data.json', SINGLE_CLOB) as j";

For testing purposes, the code runs only once. If I execute it again, I get the following message:

There is already an object named 'unparsed2' in the database.

I've also tried passing the object as an argument inside the T-SQL script, which results in an error (but receive an error near the first key value - probably due to the semicolon in the json object).

@"declare @json nvarchar (max) = " +json_object+ " "INSERT INTO unparsed SELECT * FROM OPENJSON(@json) AS json)";

Incorrect syntax near 'device'.

My JSON object:

{
  "device":"1234",
  "status":1,
  "level":100,
  "dni":true,
  "value":50
}

Upvotes: 0

Views: 2818

Answers (1)

Milney
Milney

Reputation: 6427

After the table is created you cannot create it again (SELECT ... INTO will create the table). Use the standard insert syntax instead;

@"INSERT unparsed2 (BulkColumn) SELECT BulkColumn FROM OPENROWSET(BULK 'C:\\JSON\\data.json', SINGLE_CLOB) as j";

Upvotes: 1

Related Questions