Importing Json files into SQL Server

19 Dec


New to SQL 2016 is the availability to use native JSON support to import and manipulate JSON format data, This support is also in Azure SQLDB.

Setup and Code:

Note: Your database compatibility has to be 130 or higher.

An option if you can’t set your main database to 130 or higher compatibility is to use a staging database and copy your final data to the destination which is on a lower compatible setting.


First thing needed is a SQL Database and table to land the data. A project I worked on was pulling Disaster Summary data from the OPEN Fema web services, this data was then married up to mortgage loans to see who deserved a break on payment and for how long due to a disaster occurring in their area.

  1. Create the database ( Compatibility 130 or higher)
  2. Create the table to store the imported data ( check for column null ability on the Fema Webite and set appropriately for a Production solution)

CREATE TABLE [dbo].[DisasterDeclarationsSummary](
	[id] [varchar](128) NULL,
	[disasterNumber] [int] NULL,
	[ihProgramDeclared] [bit] NULL,
	[iaProgramDeclared] [bit] NULL,
	[hmProgramDeclared] [bit] NULL,
	[totalAmountOnaApproved] [bit] NULL,
	[paProgramDeclared] [bit] NULL,
	[totalObligatedAmountCatAb] [bit] NULL,
	[fyDeclared] [int] NULL,
	[disasterType] [varchar](50) NULL,
	[incidentType] [varchar](50) NULL,
	[title] [varchar](500) NULL,
	[state] [varchar](50) NULL,
	[declarationDate] [varchar](128) NULL,
	[incidentBeginDate] [varchar](128) NULL,
	[incidentEndDate] [varchar](128) NULL,
	[disasterCloseOutDate] [varchar](128) NULL,
	[declaredCountyArea] [varchar](75) NULL,
	[placeCode] [varchar](75) NULL

3.Build the stored procedure to import the data. Here I am showing the procedure importing 1 file, but in Production you could have a wrapper application that imports many files and feeds each to this procedure. In the case of Fema and many other web services they only provide 1000 records at a time so you will need some wrapper to step through the dataset and pull back each set of data until you hit the end.

CREATE PROCEDURE [dbo].[ImportFemaJson]

@strFileName NVARCHAR (255) 



DECLARE @DisasterSummariesJson_Out NVARCHAR(MAX)
DECLARE @DisasterSummariesJson NVARCHAR(MAX)

SET @SqlStmt= 
'Select @DisasterSummariesJson =  BulkColumn FROM OPENROWSET (BULK ''' + @strFileName + ''', SINGLE_BLOB) as json';

EXEC sp_executesql @sqlStmt, N'@DisasterSummariesJson VARCHAR(MAX) OUT', @DisasterSummariesJson_Out OUT

IF (ISJSON(@DisasterSummariesJson_Out) =1) 
INSERT INTO  [dbo].[DisasterDeclarationsSummary] ([id], [disasterNumber], [ihProgramDeclared], [iaProgramDeclared], [hmProgramDeclared], [totalAmountOnaApproved], [paProgramDeclared], [totalObligatedAmountCatAb], [fyDeclared], [disasterType], [incidentType], [title], [state], [declarationDate], [incidentBeginDate], [incidentEndDate], [disasterCloseOutDate], [declaredCountyArea], [placeCode])
SELECT [id], [disasterNumber], [ihProgramDeclared], [iaProgramDeclared], [hmProgramDeclared], [totalAmountOnaApproved], [paProgramDeclared], [totalObligatedAmountCatAb], [fyDeclared], [disasterType], [incidentType], [title], [state], [declarationDate], [incidentBeginDate], [incidentEndDate], [disasterCloseOutDate], [declaredCountyArea], [placeCode]
FROM OPENJSON (@DisasterSummariesJson_Out,  '$.DisasterDeclarationsSummaries')

[id] varchar(128) '$.id',
[disasterNumber] int  '$.disasterNumber', 
[ihProgramDeclared] bit '$.ihProgramDeclared' , 
[iaProgramDeclared] bit '$.iaProgramDeclared', 
[hmProgramDeclared] bit '$.hmProgramDeclared', 
[totalAmountOnaApproved] int '$.totalAmountOnaApproved', 
[paProgramDeclared] bit '$.paProgramDeclared', 
[totalObligatedAmountCatAb] bit  '$.totalObligatedAmountCatAb', 
[fyDeclared] int  '$.fyDeclared', 
[disasterType] varchar(75) '$.disasterType',
[incidentType] varchar(75) '$.incidentType',
[title] varchar(500) '$.title',
[state] varchar (75) '$.state',
[declarationDate] datetime '$.declarationDate', 
[incidentBeginDate] varchar(128) '$.incidentBeginDate',
[incidentEndDate] varchar(128) '$.incidentBeginDate',
[disasterCloseOutDate] varchar(128) '$.disasterCloseOutDate',
[declaredCountyArea] varchar(50) '$.declaredCountyArea',
[placeCode] varchar(75) '$.placeCode'


      4. Call the procedure passing in the file you downloaded from the webservice:

DECLARE	@return_value int

EXEC	@return_value = [dbo].[ImportFemaJson]
		@strFileName = N'\\TestFiles\JSON\DisasterDeclarationsSummaries.json'

SELECT	'Return Value' = @return_value


Assuming a successful completion you should now see the data in your table.


Information on Terms:

JSON – JavaScript Standard Object Notation


Subject Links:

Open Fema Data Sets –

Leave a Reply

Your email address will not be published. Required fields are marked *