December 15, 2014 at 7:53 am
Hi,
I want to create a table dynamically with sparse columns by selecting the records coming from a stored procedure.
How do i modify my below query to do that? Thanks much!
create procedure dbo.bear_load
as
set nocount on;
Declare @cols as NVARCHAR(MAX), @query as NVARCHAR(MAX), @Result as NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(FIELD_NAME)
from bear_crossjoin
group by Field_Name, FIELDNUMBER
order by FIELDNUMBER
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N'
from
(
select substring, Field_Name,
rn = row_number() over(partition by field_name order by fieldnumber)
from bear_crossjoin
) x
pivot
(
max(substring)
for Field_Name in (' + @cols + N')
) p '
set @Result= ' select ' + @query
EXEC (@query)
GO
December 15, 2014 at 9:12 am
Any suggestions??:w00t::w00t:
December 15, 2014 at 12:41 pm
Quick suggestion, on SQL Server 2008 and earlier you can use SET FMTONLY, on 2012 and later use sp_describe_first_result_set
😎
December 15, 2014 at 12:48 pm
How does that create a dynamic table with sparse columns?
December 15, 2014 at 12:52 pm
arunkumarcg (12/15/2014)
How does that create a dynamic table with sparse columns?
It doesn't create the table, it provides all the information about the result set needed to create the table
😎
December 15, 2014 at 1:04 pm
I do this and get error
SET FMTONLY ON
GO
USE BO_DS_Staging
GO
SELECT *
FROM BO_DS_Staging.dbo.bear_load
GO
Msg 208, Level 16, State 3, Line 1
Invalid object name 'BO_DS_Staging.dbo.bear_load'.
Here bear_load is the function, it only takes a table.
December 16, 2014 at 10:37 am
Any one any help very much appreciated.
December 16, 2014 at 11:07 am
A quick example of sp_describe_first_result_set
😎
DECLARE @RES TABLE
(
is_hidden BIT NULL
,column_ordinal INT NULL
,name SYSNAME NULL
,is_nullable BIT NULL
,system_type_id INT NULL
,system_type_name SYSNAME NULL
,max_length INT NULL
,precision INT NULL
,scale INT NULL
,collation_name SYSNAME NULL
,user_type_id INT NULL
,user_type_database SYSNAME NULL
,user_type_schema SYSNAME NULL
,user_type_name SYSNAME NULL
,assembly_qualified_type_nameSYSNAME NULL
,xml_collection_id INT NULL
,xml_collection_database SYSNAME NULL
,xml_collection_schema SYSNAME NULL
,xml_collection_name SYSNAME NULL
,is_xml_document BIT NULL
,is_case_sensitive BIT NULL
,is_fixed_length_clr_type BIT NULL
,source_server SYSNAME NULL
,source_database SYSNAME NULL
,source_schema SYSNAME NULL
,source_table SYSNAME NULL
,source_column SYSNAME NULL
,is_identity_column BIT NULL
,is_part_of_unique_key BIT NULL
,is_updateable BIT NULL
,is_computed_column BIT NULL
,is_sparse_column_set BIT NULL
,ordinal_in_order_by_list BIT NULL
,order_by_is_descending BIT NULL
,order_by_list_length BIT NULL
,tds_type_id INT NULL
,tds_length INT NULL
,tds_collation_id INT NULL
,tds_collation_sort_id INT NULL
)
INSERT INTO @RES
EXEC sp_describe_first_result_set N'SELECT * FROM SYS.OBJECTS'
SELECT
*
FROM @RES
December 16, 2014 at 11:32 am
Here sysobjects is a table.
I do not have a table, i have to create one dynamically with sparse columns.
Thats my question.
December 16, 2014 at 11:49 am
arunkumarcg (12/16/2014)
Here sysobjects is a table.I do not have a table, i have to create one dynamically with sparse columns.
Thats my question.
The code demonstrates how to use sp_describe_first_result_set to get all the information necessary to be able to build a table to accommodate the output of which ever query passed as long as it returns a result set. Without this information, there is no way of using an insert / exec statement.
😎
December 16, 2014 at 12:24 pm
Ok thanks.
But how will i apply this in my query?
Instead of EXEC sp_describe_first_result_set, should i mention my proc name?
What should i do here Select * from sys_objects?
December 16, 2014 at 1:21 pm
arunkumarcg (12/16/2014)
Ok thanks.But how will i apply this in my query?
Instead of EXEC sp_describe_first_result_set, should i mention my proc name?
What should i do here Select * from sys_objects?
Replace "Select * from sys_objects" with whatever query you have that produces the desired result set, it is used in the code for demonstration as all sql server have this system view.
Then query the table variable in the code to construct the table.
😎
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply