Q.--> An alternative of
OpenRowSet in SQL Server
I have created a procedure which uses Dynamic
PIVOT to get a result. I have a table which contains column DateRange with values Jan-2014 Feb-2014 Mar-2014 etc...
Using dynamic query in
PIVOT, I am able to get the desired output by passing columns from table. The
@Columns variable is set by using
Stuff function with
FOR XML Path.
By passing this
@Columns variable into dynamic sql query for
PIVOT I am able to achieve the following:
Col1 | Jan-2014 | Feb-2014 | ............... ------------------------------------------------------- ABC | 123 |11231 | ............... ASDF |123123 |123123 | ...............
Now I have to create a physical table which will be having a result from the above procedure as shown in the table. As I mentioned the procedure is containing dynamic query, it is having
EXECUTE. Because of which I am not able to create a table inside the procedure.
I tried to create a view as well but that also didn't worked as my procedure contains variable declaration.
To achieve this I used
OpenRowSet in a script that runs on a daily basis.
My problem is: I cannot use
OpenRowSet, and I am not able to create a physical table from a stored procedure. The
OpenRowSet contains connection string that is why I cannot use it.
My procedure for creating dynamic
DECLARE @Columns AS NVARCHAR(MAX) ,@Query AS NVARCHAR(MAX) SELECT @Columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(DateRange) FROM View ORDER BY ',' + QUOTENAME(DateRange) FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 1, '') SET @Query = 'SELECT Col1, Col2, ' + @Columns + ' FROM (SELECT Col1, Col2, [DateRange], Col3 FROM View) A PIVOT (SUM(Col3) FOR DateRange IN (' + @Columns + ') ) S ' Execute (@Query)
and script for creating a physical table
SELECT * INTO Physicaltable FROM OPENROWSET('SQLOLEDB', 'ConnectionString', 'SET FMTONLY OFF;SET NOCOUNT ON;EXECUTE (AboveProcedure)')