Insert json data with repeated columns to sql server

Question

I have a JSON Data with Repeated columns with differenet data I need to insert at a time in to sql Server.Here CommonCategoryRowId is same and CommonCategoryAttributeName will change

Here is the sample of JSON Data

"[{\"CommonCategoryAttributeName\":\"Gopal\",\"CommonCategoryRowId\":1},{\"CommonCategoryAttributeName\":\"Reddy\",\"CommonCategoryRowId\":1},{\"CommonCategoryAttributeName\":\"vinnamala\",\"CommonCategoryRowId\":1}]"

score 0 · Answer 1 · edited May 23 '17 at 10:26

There's a answer that deals with something similar here Parse JSON in TSQL If refers to an article here Consuming JSON Strings in SQL Server

Using the function above, you can parse your JSON into a table, and then select from there to do your insert

SELECT *
INTO #Tmp
FROM dbo.parseJSON(
'[
{"CommonCategoryAttributeName":"Gopal","CommonCategoryRowId":1},
{"CommonCategoryAttributeName":"Reddy","CommonCategoryRowId":1},
{"CommonCategoryAttributeName":"vinnamala","CommonCategoryRowId":1}]"') data
WHERE Object_id is NULL

SELECT a.StringValue, b.StringValue
FROM #Tmp a
    LEFT JOIN #Tmp b ON b.Name = 'CommonCategoryAttributeName' AND a.Parent_id = b.parent_id
WHERE a.Name = 'CommonCategoryRowId'

And this is the result

Hope that helps

1   Gopal
1   Reddy
1   vinnamala

score 0 · Answer 2 · answered Jan 09 '15 at 11:08

public string PartyID(Stream ABC)//json that i posted
    {   
        DataTable dt = new DataTable();

        string response = string.Empty;
        try
        {
            string Json = string.Empty;

            StreamReader sr = new StreamReader(ABC);
            dynamic param = JsonConvert.DeserializeObject<dynamic>(sr.ReadToEnd());


            string CommonCategoryAttributeName= param.CommonCategoryAttributeName;
            string CommonCategoryRowId= param.CommonCategoryRowId;

            string[] commonCategoryAttributeName= CommonCategoryAttributeName.Split(new char[] { ',' });


            DataTable tbl = new DataTable();
            tbl.Columns.Add("CommonCategoryAttributeName", typeof(string));
            tbl.Columns.Add("CommonCategoryRowId", typeof(long));



            for (int i = 0; i < CommonCategoryAttributesRowId.Length; i++)
            {
                tbl.Rows.Add(CommonCategoryAttributeName[i], commonCategoryRowId);
            }

            string json = JsonConvert.SerializeObject(tbl, Formatting.None);
            json = Regex.Unescape(json);

            dt = (DataTable)JsonConvert.DeserializeObject(json.Trim(new Char[] { ' ', '"', '.' }), typeof(DataTable));

            SqlParameter[] parameters = { new SqlParameter("@commonAttributes", dt) };
            int result = yourclass.ExecuteNonQuery(null, CommandType.StoredProcedure, "commonID", parameters);

        }


        catch (Exception Ex)
        {

        }
        return response;
    }

Insert json data with repeated columns to sql server

2 Answers2