SQL Server Dynamic insert script JSON For

Question

43/5000 First of all I apologize for my bad english... I am trying to write the insert procedures of all tables with a single procedure. But I have a problem like this.

DECLARE @jsondata varchar(MAX)
Set @jsondata='[{"RecordId":1,"CreatedUser":0,"CreatedDate":"2020-03-26T14:49:21.210","UpdatedDate":"2020-03-26T14:57:33.420","UpdatedUser":0,"Status":true,"IsDeleted":false,"Name":"Oyun Konsolları","Icon":"videogame_asset","Description":"Oyun Konsolları","Order":1}]';

DECLARE @cn nvarchar(50) 
DECLARE @dt nvarchar(50) 
DECLARE @ml nvarchar(50) 
DECLARE @inserttext varchar(MAX)
DECLARE @selecttext varchar(MAX)
DECLARE @jsoncol varchar(MAX)
DECLARE @tablename varchar(50)
SET @tablename = 'Categories'
SET @inserttext = ' INSERT INTO '+@tablename+' ( ';
SET @selecttext = ' SELECT ';
SET @jsoncol = ') WITH (';

DECLARE @schema nvarchar(max) = N''

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id

WHERE
    c.object_id = OBJECT_ID(@tablename)

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @cn,@dt,@ml
WHILE @@FETCH_STATUS = 0
BEGIN 
IF(@cn NOT IN('CreatedUser','CreatedDate','UpdatedDate','UpdatedUser','Status','IsDeleted','RecordId','Status','IsDeleted'))
        BEGIN
            --Do something with Id here
            SET @inserttext = @inserttext + '['+@cn + '], ';
            SET @selecttext = @selecttext + '['+ @cn + '], ';

            IF(@dt = 'varchar' OR @dt='nvarchar' )
            BEGIN
                SET @jsoncol = @jsoncol +      '['+@cn + '] ' + @dt + ' (' +@ml + '), '
            END
            ELSE
            BEGIN
                SET @jsoncol = @jsoncol +   '['+@cn + '] ' + @dt +', '
            END
        END
    FETCH NEXT FROM MY_CURSOR INTO @cn,@dt,@ml
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

SET @jsoncol = LEFT(@jsoncol, LEN(@jsoncol) - 1)
SET @inserttext = LEFT(@inserttext, LEN(@inserttext) - 1)
SET @selecttext = LEFT(@selecttext, LEN(@selecttext) - 1)
SET @inserttext =@inserttext + ' )';
SET  @jsoncol = @jsoncol + ' )';


EXEC( @inserttext + ' '+ @selecttext + ' ' +'   FROM OPENJSON('+@jsondata+ @jsoncol);

Error i get after running :

Msg 103, Level 15, State 4, Line 1

The identifier that starts with '{"RecordId":1,"CreatedUser":0,"CreatedDate":"2020-03-26T14:49:21.210","UpdatedDate":"2020-03-26T14:57:33.420","UpdatedUser":0,"S' is too long. Maximum length is 128.

Completion time: 2020-09-25T10:42:41.2474477+03:00

29/5000

Is it possible ?

in short, is it possible to insert into tables from json as dynamic exec

Answer 1

declare @sql nvarchar(max) = @inserttext + ' '+ @selecttext + ' ' +'   FROM OPENJSON(@thejsondata'+ @jsoncol;
exec sp_executesql @stmt=@sql, @params=N'@thejsondata nvarchar(max)', @thejsondata = @jsondata;