CODEWITHSUNDEEP
sqlvisual-studio
Dmytro Kosh
Dmytro Kosh

Reputation: 93

is there a good way to import .json file content using .sql script file?

we have SQL project in Visual Studio with several .sql script files in it to populate data to Microsoft SQL Server. some .sql scripts import JSON data, which now is defined as a raw string inside sql files. I was thinking on extracting JSONs to separate files. What I found is examples like this

SELECT * FROM OPENROWSET (BULK '**C:\**sampledata.txt', SINGLE_CLOB) as importData

but they all have full path to source file, which is ridiculous and probably fail if run on another machine with different folder structure. Is there a way to embed .json files into SQL project and reference them from .sql file in more flexible way, to guarantee that compiled script will work?

Upvotes: 0

Views: 438

Answers (1)

Trevor Burn
Trevor Burn

Reputation: 11

I've looked into this myself, and I don't think it'll work.

  • The OPENROWSET BULK method of reading data from a file only accepts a full path.
    • You could try and get around this with a pre/post-deployment script and some SQLCMD variables, but then you run into the next issue.
  • OPENROWSET will only work if the file can be accessed from the machine running the query. If you run this against a remote server, you'll have to jump through some hoops to give it access to your data file.
  • Also, if you want to do this from an Azure SQL database, you'll need to store the data in Azure Blob Storage.

OPENROWSET documentation

Here's the best recommendation I can provide. Assuming that your primary goals are to a) ensure legibility in the code and b) allow for a more clear diff when updating the JSON data, create separate scalar-valued functions for each of your JSON strings. Each function will simply return your raw JSON string. Not exactly creative, but it will allow you to format your JSON for maximum legibility while allowing you to clean up the scripts that actually use this data.

e.g. Instead of having one file that looks like this:

DECLARE @json NVARCHAR(MAX) = N'[{"attr":"value 1"},{"attr":"value 2"},{"attr":"value 3"},{"attr":"value 4"},{"attr":"value 5"},{"attr":"value 6"},{"attr":"value 7"},{"attr":"value 8"},{"attr":"value 9"},{"attr":"value 10"}]'
--do stuff with the @json

Have two files, one:

CREATE FUNCTION GetJson()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN N'
[
  {
    "attr": "value 1"
  },
  {
    "attr": "value 2"
  },
  {
    "attr": "value 3"
  },
  {
    "attr": "value 4"
  },
  {
    "attr": "value 5"
  },
  {
    "attr": "value 6"
  },
  {
    "attr": "value 7"
  },
  {
    "attr": "value 8"
  },
  {
    "attr": "value 9"
  },
  {
    "attr": "value 10"
  }
]
'
END

and two:

DECLARE @json NVARCHAR(MAX) = dbo.GetJson()
--do stuff with @json

Upvotes: 1

Related Questions