Insert JSON into SQL Server 2016 using C# and OPENJSON

Question

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
}

Milney · Accepted Answer · 2017-04-20 12:17:23Z

1

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";

answered Apr 20, 2017 at 12:17

Milney

6,4472 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Rapsoulis Over a year ago

Didn't even think about it! Thanks, worked like a charm!. Follow up Q: If I already have a JSON Object (not in a file), how can I pass it within the T-SQL as an argument? ... (BULK 'json object' , SINGLE_CLOB) ...

Milney Over a year ago

When you say a JSON object... do you mean in a SQL variable? Or in your C# code? If C# - use a SqlParameter, something like so: csharp-station.com/Tutorial/AdoDotNet/Lesson06

Rapsoulis Over a year ago

In my C# code. It's a "JObject" from the Newtonsoft package. So according to the tutorial, I just have to declare the parameter and then add it to the command using the "ParameterName". Correct?

Rapsoulis Over a year ago

I get this error: "No mapping exists from object type Newtonsoft.Json.Linq.JObject to a known managed provider native type."

cmd.Connection = conn; SqlParameter param = new SqlParameter(); param.ParameterName = "@json"; param.Value = o2; cmd.Parameters.Add(param); cmd.CommandText = @"INSERT unparsed2 (BulkColumn) SELECT BulkColumn FROM OPENROWSET(BULK '@json', SINGLE_CLOB) as j";

try {conn.Open(); cmd.ExecuteNonQuery();} catch (SqlException e) {}

Milney Over a year ago

You may need to do o2.ToString() ??

|

Collectives™ on Stack Overflow

Insert JSON into SQL Server 2016 using C# and OPENJSON

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related