CODEWITHSUNDEEP
sqljsonsql-server
Santanu
Santanu

Reputation: 347

How to use dynamic table_name in OPENJSON query?

I have a query like :

DECLARE @jsonVariable NVARCHAR(MAX)
SET @jsonVariable =  
N'{ "id" : "11","info": { "fname": "John", "surname": "Smith" }, "age": "25" }'  

INSERT INTO Employees
SELECT *  
FROM OPENJSON(@jsonVariable)  
  WITH (id int,fname nvarchar(50) '$.info.fname')

How to use a dynamic TableName after INSERT INTO instade of Employees?

I have tried with sp_executesql:

DECLARE @jsonVariable NVARCHAR(MAX)
DECLARE @TABLENAME NVARCHAR(MAX) = 'Students'
DECLARE @SQL NVARCHAR(100)

SET @jsonVariable =  N'{ "id" : "11","info": { "fname": "John", "surname": "Smith" }, "age": 25 }'   

SET @SQL = 'INSERT INTO '+ @TABLENAME +' SELECT *  FROM 
OPENJSON(@jsonVariable); WITH (id int ,age nvarchar(50))'
EXEC sp_executesql @SQL

But getting the error : Must declare the scalar variable "@jsonVariable". Is there any way to do it?

Upvotes: 0

Views: 804

Answers (1)

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please use like this.

CREATE TABLE Em
(
     Id INT
    ,fName VARCHAR(10)
)
GO

DECLARE @jsonVariable NVARCHAR(MAX)
DECLARE @TableName VARCHAR(10) = 'Em'
SET @jsonVariable =  
    N'{ "id" : "11","info": { "fname": "John", "surname": "Smith" }, "age": 25 }'  

DECLARE @SQL AS VARCHAR(MAX) = '

INSERT INTO ' + @TableName + '
SELECT *  
FROM OPENJSON(' + '''' + @jsonVariable + '''' + ')  
  WITH (id int,fname nvarchar(50) ''$.info.fname'') '

EXEC(@SQL)
SELECT * FROM Em

OUTPUT

Id          fName
----------- ----------
11          John

(1 rows affected)

Upvotes: 3

Related Questions