This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 36%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESD%3C/text%3E%3C/svg%3E)
I have a table with one column where json data are exported from a external data source.
How do I convert this josn into SQL server table.
Please find the table script below
CREATE TABLE [dbo].[test](
[text] nvarchar NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[test] ([text]) VALUES (N'[')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151952+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-09-14",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151952",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 99,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' },')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151944+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-09-14",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151944",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 97,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' },')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151940+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-09-14",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151940",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 95,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' },')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151936+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-09-14",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151936",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 93,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' },')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151931+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-10-16",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151931",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": true,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 91,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' }')
GO
INSERT [dbo].[test] ([text]) VALUES (N']')
GO
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
I updated the answer on how to use bcp utility.
Did you have a chance to try a bcp based solution?
Is it possible to have a json file instead of such table? It is probably easier to import the data from a file. Do you already have the definition of the target table and columns, or you want to deduce this from json?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 46%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
Hi @sourav dutta ,
Are there any updates on this issue?
Did the answers help you?
If you have any other question, please feel free to let us know.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(240, 3%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EG%3C/text%3E%3C/svg%3E)
If your SQL server version is 2017 or higher, try the function STRING_AGG():
Since the datatype of [text] is nvarchar(max), I added N before the separator ' ':
SELECT STRING_AGG([text], N' ') AS jsonCol
FROM [dbo].[test];
Thanks for your quick reply.
In this shared tables it is working perfectly.
But I have a table where more than 20000 rows are there.
The shared query not able to fetch all rows.
Can you please help me on this.
You mean that more than 20000 rows make one JSON string?
Yes. Exactly.
How could I do that?
What is the data type of the column with more than 20000 rows?
Datatype is nvarchar(max) for the text column in test table
I think that concatenation will not always work perfectly if the rows are unordered.
Then, what will be solution, I will go.
The shared query not able to fetch all rows.
What does it mean? If the data type is nvarchar(max) of that column, the return string type is also nvarchar(max). Did you try it? Any error message?
I updated my answer. Check it out.
Based on Microsoft documentation of STRING_AGG(), it does not mention the issue of the order. Otherwise, it should do it like the function STRING_SPLIT().
Hi, Answer is not updated.
Please try the following approach for your real life table.
SQL
USE tempdb;
GO
DROP TABLE IF EXISTS dbo.test;
CREATE TABLE [dbo].[test](
[text] [nvarchar](max) NULL
)
INSERT [dbo].[test] ([text]) VALUES
(N'[')
, (N' {')
, (N' "id": "VN110001+70151952+2",')
, (N' "StartingWeekAnchorDate": "2020-09-14",')
, (N' "CustomerId": "70151952",')
, (N' "Frequency": 7,')
, (N' "Active": false,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 99,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' },')
, (N' {')
, (N' "id": "VN110001+70151944+2",')
, (N' "StartingWeekAnchorDate": "2020-09-14",')
, (N' "CustomerId": "70151944",')
, (N' "Frequency": 7,')
, (N' "Active": false,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 97,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' },')
, (N' {')
, (N' "id": "VN110001+70151940+2",')
, (N' "StartingWeekAnchorDate": "2020-09-14",')
, (N' "CustomerId": "70151940",')
, (N' "Frequency": 7,')
, (N' "Active": false,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 95,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' },')
, (N' {')
, (N' "id": "VN110001+70151936+2",')
, (N' "StartingWeekAnchorDate": "2020-09-14",')
, (N' "CustomerId": "70151936",')
, (N' "Frequency": 7,')
, (N' "Active": false,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 93,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' },')
, (N' {')
, (N' "id": "VN110001+70151931+2",')
, (N' "StartingWeekAnchorDate": "2020-10-16",')
, (N' "CustomerId": "70151931",')
, (N' "Frequency": 7,')
, (N' "Active": true,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 91,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' }')
, (N']');
DECLARE @JSON NVARCHAR(MAX) = N'';
SELECT @JSON += text
FROM dbo.test;
SELECT @JSON;
-- UPDATE --
Another approach to use bcp.exe to generate JSON file out of this table.
The rest is trivial.
DECLARE @SQLCmd VARCHAR(8000)
, @outputFileName VARCHAR(256) = 'e:\Temp\output.json'
, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT [text] FROM tempdb.dbo.test;'
, @tcpPort VARCHAR(10) = '1433'
, @WindowsAuth BIT = 1 -- 1 is for Windows Authentication, 0 is for SQL Server Authentication
, @loginID VARCHAR(100) = 'loginID'
, @password VARCHAR(100) = 'password'
, @serverName VARCHAR(100) = 'SPACESHIP';
-- /B "WindowTitle" parameters produce output in the SSSMS !!!
SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"'
+ ' "' + @SQL + '"'
+ ' queryout "' + @outputFileName + '"'
--+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
+ CASE WHEN @WindowsAuth = 1 THEN ' -T'
ELSE ' -U '+ @loginID + ' -P ' + @password
END
+ ' -c -C 65001 -a 32768'
+ ' -S "' + @serverName + ',' + @tcpPort + '"';
-- just to see it
SELECT @SQLCmd AS [Command to execute];
-- create file on the file system
EXECUTE master.sys.xp_cmdshell @SQLCmd;
Hi, I used this solution and it taking too much time and still executing from 35 mins because of huge data(approx 20k in test table)
Is any other way, I can do that using "STRING_AGG" function or anything which will faster.
I updated my answer with another suggestion to use bcp utility. Check it out.
How I use bcp utility.
Can you please guide me once.
I updated the answer. It is very fast. Check it out.