How to use dynamic table_name in OPENJSON query?

Question

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?

You've basically got it. Change @jsonVariable to '+@jsonVariable+' to pass the variable into your dynamic SQL string — fez
– fez, Commented Mar 29, 2018 at 14:31
You are defining the variable outside of the executesql so it does not know what it is. — dfundako
– dfundako, Commented Mar 29, 2018 at 14:31

Pawan Kumar · Accepted Answer · 2018-03-29 14:32:29Z

3

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)

answered Mar 29, 2018 at 14:32

Pawan Kumar

2,02112 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

How to use dynamic table_name in OPENJSON query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related