Convert JSON to CREATE TABLE Statements

In this episode I share a query I wrote to automatically parse a JSON object into SQL Server CREATE TABLE statements.

Related blog post with example code:

Github:

Twitter:

[WilburSeanBvuma]

been looking all over the net for a function like yours and nothing, great work Bert.

[antyst]

Thanks. I haven't tried it yet, but your effort and sharing are really appreciated, and I'm sure your query will be helpful, at least to certain degrees, for people who need to create database schema and load data from json files.

[NigelStratton]

Bro, awesome stuff. There's a real chance you spent longer writing it than converting JSON into SQL tables by hand but it's the satisfaction of using it and knowing others benefit from it as well!

[vimalraj]

Great!!!! perfect sql query to convert json to sql, thanks you.

[Veretax]

This is fascinating SQL code... what I was hoping to find was a way to generate a possible table definition by inspecting a subset of a list of data from the json response of an API. For example using python, C#, etc to output a definition and get the types right, even if precision, keys etc might need modified.

[davidpui]

Hi Bert
Can you please share the code so that I can test out the conversion of JSON documents to SQL tables ?

[georgeioakimedes]

Thanks for sharing this, it looks like it works very well with my data and only a few edits are necessary. For some reason I get table structures with duplicate column definitions:

CREATE TABLE dbo.Packaging
(
PackagingId int IDENTITY(1, 1) PRIMARY KEY,

AlternatePackagingId int NULL,

PartDetailsId int NULL,

Parameter nvarchar(29) NULL,

Parameter nvarchar(29) NULL,

ParameterId int NULL,

ParameterId int NULL,

Value nvarchar(36) NULL,

Value nvarchar(36) NULL,

ValueId nvarchar(23) NULL,
ValueId nvarchar(23) NULL
)

I was wondering if you had something that then takes the JSON data and inserts it into these created tables. I just dabble in this stuff so I thought I'd ask before trying to recreate the "wheel"