declare @json varchar(4000) = '
{"sql":[{"Id":"a312b4a9-461c-45b2-bc6b-9c61fb13ab18","CommandString":"DECLARE @SessionId nvarchar(24) = N\u0027p0utee4f4wa04m14ue1p4s13\u0027;\r\n\r\nprGetUserIdFromClientLogin","ExecuteType":"Scalar","StackTraceSnippet":"GetSPField GetUserIdForSession InitializeUserAuth Page_Load ProcessRequest","StartMilliseconds":1303.5,"DurationMilliseconds":115.1,"FirstFetchDurationMilliseconds":0},{"Id":"25099cfd-45df-42da-95df-52c0c74f2e66","CommandString":"DECLARE @UserID bigint = 69512;\r\n\r\nprSessionState","ExecuteType":"Reader","StackTraceSnippet":"GetSPDataSet GetSPDataTable GetSession GetUserSessionState InitializeUserAuth Page_Load ProcessRequest","StartMilliseconds":1462.2,"DurationMilliseconds":145.6,"FirstFetchDurationMilliseconds":7.9},{"Id":"ae5beb87-2ca4-4ece-9548-7335819c5060","CommandString":"DECLARE @ClientID bigint = 69512;\r\n\r\nprClientPermissions","ExecuteType":"Reader","StackTraceSnippet":"GetSPDataSet GetSPDataTable getClientPermissions InitializeUserAuth Page_Load ProcessRequest","StartMilliseconds":1618.3,"DurationMilliseconds":1.1,"FirstFetchDurationMilliseconds":0.8}]}
'

;with parsed as (
	select e.*
	from (values(@json)) v(j)

-- Find the start of the 'sql' array
	cross apply (
		select substring(v.j, charindex('"sql":[', v.j) + 7,len(v.j)) 
		) a(sql)

-- Extract the array, discarding the rest
	cross apply (
		select substring(a.sql, 1, charindex('}]',a.sql))
		) b(sql)

-- Split the result up by starting braces
	cross apply (
		select *, rn= row_number() over(order by d.itemnumber)
		from dbo.DelimitedSplitN4K(b.sql, '{') d
		where d.item <> ''
		) c
-- Further split by ending braces (could have used substring/charinded here)
	cross apply (
		select c.rn, d.*
		from dbo.DelimitedSplitN4K(c.Item, '}') d
		where d.item not in ('',',')
		) d

-- Select just the bits we want, discarding delimiters
	cross apply (
		select d.rn,  p.ItemNumber, p.Item 
		from dbo.PatternSplitCM(d.Item, '%[^":]%') p
		where p.Matched = 1 and p.Item not in ('',',')
		) e
)

-- Build the output
select rn
	 , Id
	 , ExecuteType
	 , CommandString
	 , StackTraceSnippet
	 , StartMilliseconds
	 , DurationMilliseconds
	 , FirstFetchDurationMilliseconds

-- Source is the parsed output from above with a self-join based on row and item number
from (select p1.rn, p1.item p1item, p2.item p2item
  from parsed p1 
  join parsed p2 
    on p2.rn = p1.rn
  and  p2.ItemNumber = p1.ItemNumber+2) src

-- pivot on the items we want
pivot (max(p2item) 
	for p1item in ( Id
	              , CommandString
		      , ExecuteType
		      , StackTraceSnippet
		      , StartMilliseconds
		      , DurationMilliseconds
		      , FirstFetchDurationMilliseconds)) pvt

;with 
	parsed as (
		select p1.rn, p2.ItemNumber, p2.item

		-- Split into array members
		from (
			select p1.*, rn=row_number() over(order by (select 1))
			from dbo.PatternSplitCM(@json, '[^{}]') p1
		) p1

		-- split array members into key/value pairs
		cross apply  (
			select * from dbo.PatternSplitCM(p1.item, '[^{}:",[]')
			WHERE Matched = 1 and item not in ('',',')
		) p2
	)

-- Build the output
select rn
	 , Id
	 , ExecuteType
	 , CommandString
	 , StackTraceSnippet
	 , StartMilliseconds
	 , DurationMilliseconds
	 , FirstFetchDurationMilliseconds

-- Source is the parsed output from above with a self-join based on row and item number
from (select p1.rn, p1.item p1item, p2.item p2item
  from parsed p1 
  join parsed p2 
    on p2.rn = p1.rn
  and  p2.ItemNumber = p1.ItemNumber+2
  ) src

-- pivot on the items we want
pivot (max(p2item) 
	for p1item in ( Id
	              , CommandString
		      , ExecuteType
		      , StackTraceSnippet
		      , StartMilliseconds
		      , DurationMilliseconds
		      , FirstFetchDurationMilliseconds)) pvt

quote:

---

Originally posted by jeremyjsimmons

@gbritton

Cool function & technique. I don't quite understand how I can apply this to the entire table at once.
It seems to be you're still using a variable @json, and I would have to use this in the cursor?

Is there a way to use this technique in the context of the source and destination table?

Also, I tried it on a larger case, and only returned 20 out of the 127 rows

Thanks.

---

quote:

---

Originally posted by jeremyjsimmons

@gbritton. I don't see how I can avoid the use of the cursor, [dbo].[jqsql.totable] is a CLR Stored Procedure. I'm using a modified version of jqsql.com. I can post to github if you're interested.

I was able to make your simplified version work using CROSS APPLY.
I believe there are still some edge cases to work out, but otherwise it's an interesting option.
I appreciate your offer to look at the full data, but I'm going to decline from posting it.

---

quote:

---

Originally posted by jeremyjsimmons

@TG - thank you for the additional suggestions. Why is the single insert from the #temp table desirable over the insert from the @table every iteration through the loop.

The NOT IN vs LEFT OUTER JOIN WHERE ID IS NULL vs NOT EXISTS (SELECT 1 FROM Destination WHERE A.ID = Destination.ID) all have very similar query plans. Why would I choose one over the other?

---