CODEWITHSUNDEEP
sqlsql-server
Santanu
Santanu

Reputation: 347

How to give dynamic column name in WITH clause in OPENJSON query?

I have different json format and want to insert different format in different table using OPENJSON.Table name able to give dynamically.But how to give the column dynamically?My query is like :

DECLARE @jsonVariable NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)

SET @jsonVariable =  
    N'{ "id" : "12","info": { "fname": "John", "surname": "Smith" },"table":"Students" }'  

SET @TableName = (SELECT JSON_VALUE(@jsonVariable , '$.table'))

DECLARE @SQL AS VARCHAR(MAX) = '
 INSERT INTO ' + @TableName + '
 SELECT *  
 FROM OPENJSON(' + '''' + @jsonVariable + '''' + ')  
 WITH (id int,fname nvarchar(50) ''$.info.fname'') '

EXEC(@SQL)

In Students table, have only 2 columns : id and fname.So passing id and fname in WITH clause . Say i have another json :

SET @jsonVariable =  N'{ "id" : "12","fname": "John", "lname": "Smith", "age": 25, "table":"Employees" }'

In Employees table, have 4 columns : id,fname,lname,age. and want to put the 2nd json data in Employees table.So how to change the WITH clause dynamically or there is any other solution?

Upvotes: 2

Views: 3779

Answers (1)

Andomar
Andomar

Reputation: 238176

Here's a stored procedure that parses the JSON and inserts it in a table:

create or alter procedure dbo.InsertJson(@json nvarchar(max))
as begin
    declare @id int = json_value(@json, '$.id')
    declare @info nvarchar(max) = json_query(@json, '$.info')
    declare @table sysname = json_value(@json, '$.table')

    declare @columns nvarchar(max) = ''
    declare @values nvarchar(max) = ''
    select  @columns = @columns + case when @columns = '' then '' else ', ' end + 
                quotename([key])
    ,       @values = @values + case when @values = '' then '''' else ', ''' end + 
                replace([value], '''', '''''') + ''''
    from    openjson(@info);

    declare @sql nvarchar(max) = 
        'insert ' + quotename(@table) + 
        '       (id, ' + @columns + ') ' +
        'values (' + cast(@id as varchar) + ', ' + @values + ')';
    exec(@sql)
end

Example usage:

create table t1 (id int, col1 int);
create table t2 (id int, col1 int, col2 int);
exec dbo.InsertJson '{ "id" : "1", "info": { "col1": "1"}, "table":"t1" }';
exec dbo.InsertJson '{ "id" : "1", "info": { "col1": "1", "col2": "2"}, "table":"t2" }';
select * from t1;
select * from t2;

Upvotes: 3

Related Questions