Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server

Question

Q.--> An alternative of OpenRowSet in SQL Server

Description:

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:

Result table:

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.

Please suggest...

EDIT 1:

My procedure for creating dynamic PIVOT table:

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)')

Answer 1

If you cannot use OPENROWSET, you could create a loopback linked server and use OPENQUERY or EXECUTE AT.

DECLARE @srv nvarchar(4000);
SET @srv = @@SERVERNAME; -- gather this server name

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server     = N'LOOPBACK',
@srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
@provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
@datasrc    = @srv;


-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname  = N'LOOPBACK',
@useself     = N'True',
@locallogin  = NULL,
@rmtuser     = NULL,
@rmtpassword = NULL;

I blogged about it some years ago.

Another option is using some kind of CLR trickery to interpret the result set definition and create a target table based on the type and length of the columns.

If you're not allowed to use OPENROWSET, I highly doubt you will be allowed to use CLR, though. In this case, a third option could be using sp_describe_first_result_set and code your table creation script in T-SQL. It requires SQL Server 2012 or newer.

Answer 2

Solution for this :

SET @Query = 'SELECT Col1, Col2, ' + @Columns + ' INTO PhysicalTable
              FROM (SELECT Col1, Col2, [DateRange], Col3 FROM View) A
               PIVOT
                 (SUM(Col3) FOR DateRange IN (' + @Columns + ') ) S '

Execute (@Query)

And now I am able to remove the OpenRowSet.