Get Stored Procedure Result to A Table


--USE yourDB 
----Get Stored procedure result into a table

;with mycte as (
SELECT distinct, r.column_ordinal, colName,r.system_type_name,r.is_nullable 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
WHERE not like'sp_%' --no system sp
and ='getYourSpName'  


  SELECT   N'If Object_id('''+QUOTENAME('Show_SpResult_'''',''U'') is not null DROP Table '
  + QUOTENAME('Show_SpResult_' 
  + ' Create table '
  + QUOTENAME('Show_SpResult_' 
  +'('+ STUFF((SELECT  ', ' + QUOTENAME(Colname) + ' ' 
  + system_type_name + Case when is_nullable=1 then  ' NULL' Else ' Not NULL' END
	  FROM mycte AS m2
	  ORDER BY m2.column_ordinal
	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
	  +');  INSERT INTO '+QUOTENAME('Show_SpResult_' ' EXEC '+ Name 
	  + ';  SELECT * FROM '+QUOTENAME('Show_SpResult_' +';' as QueryToBeExecuted
 FROM mycte AS m1
 Group by name

 --Copy the result and execute to get the result (you need to pass in parameters' values if your sp has them )
 --We can make this script to run dynamically

 --More information about sys.dm_exec_describe_first_result_set_for_object 


Sample Query for a Question: “Select IN and OUT time based on flag”


create table #temp (EmpID varchar(20),CheckedTime datetime,CheckedType varchar(1))
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 09:00:10.000','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 12:55:00.305','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:40:20.111','I')
--insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:44:10.224','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:45:15.224','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:58:00.003','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 15:10:30.030','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:40:40.156','O')
--insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:44:00.601','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:45:00.601','O') 

;with mycte as (
select *, row_number() Over(Partition by EmpID Order by CheckedTime)
-row_number() Over(Partition by EmpID, CheckedType Order by CheckedTime) grp
 from #temp)
 ,mycte1 as (
 Select *
 , row_number() Over(Partition by EmpID, CheckedType, grp Order by CheckedTime ASC) rnFirst
 , row_number() Over(Partition by EmpID, CheckedType, grp Order by CheckedTime DESC) rnLast
   from mycte)

 Select EmpID,CheckedTime, CheckedType
 ,Case when rnFirst=1 and rnLast>1 then 'First_'
 when rnFirst>1 and rnLast=1 then 'Last_'
 End +CheckedType as checkCol 
 from mycte1
  Order by CheckedTime

drop table #temp

A Sample to Transform JSON File from to SQL Server Table (SQL Server 2016)

--Sample to download json file with your own apikey (paid subscription )

---a table to hold json file data
Drop  table  if exists dbo.RawData_JsonFileTemp

Declare @sql nvarchar(max);
Declare @ColumnHeaders VARCHAR(MAX) 

declare @json1 varchar(max) 
SELECT @json1=BulkColumn--,isjson(BulkColumn)
  FROM OPENROWSET (BULK 'C:\temp\JSON\getGrainBids.json', SINGLE_CLOB) t

;with mycteSrc as (
 SELECT  c.[key],  c.[value] 
 FROM OPENJSON(@json1, '$.results') as c

,myctebids as (
Select  d.value
from mycteSrc
cross apply  OPENJSON ( value,  '$.bids' )   d
,myColumnList as (
select 1 as seq, e.[key] ,   'JSON_Value (b.value, ''$.'+e.[key]+ ''') as '+ quotename(e.[key])   col
from myctebids
cross apply   OPENJSON ( value) e
Select 2 as seq,  d.[key] ,   'JSON_Value (c.value, ''$.'+d.[key]+ ''') as '+ quotename(d.[key])  
from mycteSrc
cross apply   OPENJSON ( value ) d

Select @ColumnHeaders = STUFF( (SELECT  ','   + col  
  FROM myColumnList
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
 --Print @ColumnHeaders
 Select @sql='SELECT '+@ColumnHeaders+ '
  into RawData_JsonFileTemp from OPENJSON('''+ @json1+'''  , ''$.results'') as c
 CROSS APPLY OPENJSON (c.value, ''$.bids'')  b
 --print @sql
EXEC sp_executesql @sql;

 SELECT *  FROM dbo.RawData_JsonFileTemp