NDZY1n2u

1IF OBJECT_ID('tempdb..##jsondump_personnel') IS NOT NULL DROP TABLE ##jsondump_personnel
2IF OBJECT_ID('tempdb..##jsonparsed_personnel') IS NOT NULL DROP TABLE ##jsonparsed_personnel
3IF OBJECT_ID('tempdb..##json_loop_personnel') IS NOT NULL DROP TABLE ##json_loop_personnel
4
5CREATE TABLE ##jsondump_personnel(
6    [my_json] [nvarchar](max) NULL
7) 
8
9-- Create a table to house the parsed content
10CREATE TABLE ##jsonparsed_personnel (
11    [name] [varchar](255) NULL,
12    title [varchar](255) NULL,
13    [path] [varchar](255) NULL
14)
15
16-- Clear ##jsondump
17TRUNCATE TABLE ##jsondump_personnel;
18
19-- Clear ##jsonparsed ( only if you don't want to keep what's already there )
20TRUNCATE TABLE ##jsonparsed_personnel;
21
22-- Import ( single column ) JSON
23--IMPORTANT: Need to be sure the company_data.json file actually exists on the remote server in that directory 
24BULK INSERT ##jsondump_personnel
25FROM 'C:mattermark_etl_projectpersonnel_data.json' -- ( <-- my file, point to your own )
26WITH (
27    ROWTERMINATOR = 'n'
28);
29
30-- Select JSON into ##jsonparsed
31SELECT my_json 
32INTO ##json_loop_personnel
33FROM ##jsondump_personnel;
34
35--SELECT * FROM ##jsondump;
36
37INSERT INTO ##jsonparsed_personnel (
38    [name], title, [path]
39)
40SELECT DISTINCT
41    jsn.[name], jsn.[title], jsn.[path]
42FROM ##json_loop_personnel
43OUTER APPLY (
44
45    SELECT * FROM OPENJSON(##json_loop_personnel.my_json)
46    WITH (
47        [name] VARCHAR(255) '$.name',
48        title VARCHAR(255) '$.title',
49        [path] VARCHAR(255) '$.path'
50    )
51
52) AS jsn
53
54DECLARE @bcp_cmd4 VARCHAR(1000);
55DECLARE @exe_path4 VARCHAR(200) = 
56    ' cd C:Program FilesMicrosoft SQL Server100ToolsBinn & ';
57SET @bcp_cmd4 =  @exe_path4 + 
58    ' BCP.EXE "SELECT ''Name'', ''Title'', ''Path'' UNION ALL SELECT DISTINCT name, title, path FROM t##jsonparsed_personnel" queryout ' +
59    ' "C:mattermark_etl_projectpersonnel_data.txt" -T -c -q -rn';
60PRINT @bcp_cmd4;
61EXEC master..xp_cmdshell @bcp_cmd4
62
63SELECT DISTINCT * FROM ##jsonparsed_personnel
64
65DROP TABLE ##jsondump_personnel
66DROP TABLE ##jsonparsed_personnel 
67DROP TABLE ##json_loop_personnel 
68
69exec xp_cmdshell 'C:mattermark_etl_projectpowershell "C:mattermark_etl_projectopen_personnel_file.ps1"' 
70     
71SQLState = S0002, NativeError = 208
72Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '##jsonparsed_personnel'
73SQLState = 37000, NativeError = 8180
74Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
75NULL