Push results of dynamic SQL statement into temp table

Dale Fye
Dale Fye used Ask the Experts™
on
I have created a dynamic SQL statement which creates a pivot table for records in a combination of tables.

When I run this:

Execute(@query)

it returns a recordset, but this is only an intermediate step, as this data then needs to be merged into another table.

So, how do I get the results of Execute(@query) into a temporary table which I can then use in an update query where these results are joined to the destination table on Bolo_ID and RecordDate?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dorababu MSenior Software Engineer
Distinguished Expert 2018

Commented:
You can do as follows

DECLARE @query_from NVARCHAR(MAX) = ''
DECLARE @query NVARCHAR(MAX)

CREATE TABLE #TableVariable (ID INT)
INSERT INTO #TableVariable VALUES (1),(2)

SET @query_from = @query_from + CHAR(10) + ' JOIN #TableVariable on Customer.Id = #TableVariable.ID ' 
SET @query = 'SELECT * FROM Customer ' + @query_from

EXECUTE sp_executesql @query

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
@dorababu

I really hate editing dynamic SQL (embedded quotes are a nightmare!), which is why I was hoping that after creating the query that generates that table (@Query), I could simply generate those results into a temp table (physical or memory) and use that temp table as the source in my subsequent MERGE statement.

Dale
ste5anSenior Developer

Commented:
I have created a dynamic SQL statement which creates a pivot table for records in a combination of tables. [..]
 it returns a recordset, but this is only an intermediate step, as this data then needs to be merged into another table.
Pivoting should be the last step in your processing. Then you would not need that string fiddling in the dynamic SQL string.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
The only way I've ever known to pull this off is to construct your pivot table so that the columns have defined names, such as CurrentMonth, CurrentMonthMinusOne, CurrentMonthMinusTwo, etc. such that a direct mapping can take place between your pivot table and the downstream table.

Otherwise, how would the downstream table know what columns to use?

Good luck.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
INSERT INTO #table_name --( col_name1, col_name2, ... )
EXEC(@query)

Of course the temp table must already exist.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
I'll have to test that Scott, never considered that it might be that easy.
ste5anSenior Developer

Commented:
And the column order must match..

the easy approach is to postpone the pivoting to the end of the processing.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
And the column order must match..

I don't think it does, as long as you specify the column(s) in the receiving table.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks guys.

Scott's solution was precisely what I was looking for.

The pivot has two base columns and will always contain the same three pivot columns.  This is done to get the data into a format which is easily to join with the destination table on the two base columns, allowing me to easily set the values of the three columns based on the merge.

The column order is immaterial as the merge statement specifies which columns in the target to update from which column in the source.

I know I could have probably done this more elegantly with three separate merge or update queries but I just wanted to try this.

Dale
Mark EdwardsChief Technology Officer

Commented:
Dale:

Here's a little code I use to put the results of any select query into a temp table without knowing the field names:

IF(OBJECT_ID('tempdb..#Table1','U') IS NOT NULL)
begin
     DROP TABLE #Table1
end
SELECT * INTO [#Table1] FROM (
pivot query sql
) AS qryPivot
 WHERE .....

I know you can figure out the rest....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial