Save query generated from stored procedure to table?

Hi there.

I'm working on a stored procedure that makes a pivot table. I want to build into the stored procedure the option to save the result to a table only for this particular output. And the table must be flushed before a new query result from the stored procedure is generated.
Is that possible? Any example? :-)
It's the procedure in my other question here: 29073732/Convert-MS-Access-SQL-Pivot-code-to-MS-SQL-Server-code.html

Best regards

Ulrich
ullenulleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ganesh GuruduSenior ConsultantCommented:
create new table  with the below list of columns.
SELECT
     CoursePatId
      ,MIN(FirstTreatStart1) FirstTreatStart
      ,SUM(NumberOfTreatments) NumberOfTreatments
      ,MAX([Check-point-inhib])[Check-point-inhib]
      ,MAX([Cytokine]) [Cytokine]
      ,MAX([Cytostatic drug]) [Cytostatic drug]
      ,MAX([Targeted]) [Targeted]
FROM .....

and in the procedure first TRUNCATE  this TABLE and insert  new rows in it.

e.g
INSERT INTO newtable
SELECT * FROM table1 WHERE condition; -- This should be your old/exiting  query.
Mark WillsTopic AdvisorCommented:
Yes, that is possible. However we need to fix the SP so it doesnt error.

The challenge will be the variable number of columns in the result set. But it would be easier if saved as a normal table, then, run the pivot on that table. The 'Flush' option would mean generate a new set of data, otherwise, use the data in the table.
Ganesh GuruduSenior ConsultantCommented:
be sure about the columns or the query you are trying into insert.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Author,

Basically we have to drop the existing table first and create it again on the fly as out number and names of columns are dynamic in nature.

I have added below lines..

/*Drop the table if already exists*/
--IF OBJECT_ID('tempdb..YourPhysicalTableName') IS NOT NULL
--     DROP TABLE YourPhysicalTableName;  

/*INTO YourPhysicalTableName*/
--Insert new data INTO YourPhysicalTableName

/*SELECT DATA FROM YourPhysicalTableName*/
--SELECT * FROM YourPhysicalTableName;

Changed below code for this requirement.

--

USE [mcd]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_q3TreatmentRelevant_Crosstab] 
AS

/*Declare Variables*/
DECLARE @vals AS VARCHAR(MAX) = ''
DECLARE @Maxvals AS VARCHAR(MAX) = ''

/*Get names of dynamic columns*/
SELECT 
  @vals = @vals+ '['+ TreatmentDrugHighLevel +']' + ',' 
, @Maxvals = @Maxvals + 'MAX(['+ TreatmentDrugHighLevel +']) ' + '['+ TreatmentDrugHighLevel +']' + ',' 
FROM ( SELECT DISTINCT TreatmentDrugHighLevel FROM [dbo].[view_3TreatmentRelevant] ) [view_3TreatmentRelevant]

/*Remove last comma*/
SELECT @vals = SUBSTRING(@vals,1,LEN(@vals)-1)
SELECT @Maxvals = ',' + SUBSTRING(@Maxvals,1,len(@Maxvals)-1)

IF OBJECT_ID('tempdb..YourPhysicalTableName') IS NOT NULL
     DROP TABLE YourPhysicalTableName      

/**Generate dynamic SQL*/
DECLARE @SQL AS VARCHAR(MAX) = '

SELECT 
      CoursePatId 
      ,MIN(FirstTreatStart1) FirstTreatStart
      ,SUM(NumberOfTreatments) NumberOfTreatments
      ' + @MaxVals + '

INTO YourPhysicalTableName
FROM
(
      SELECT 
      dbo.view_3TreatmentRelevant.CoursePatID, 
      MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
      MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart1,
      COUNT(dbo.view_3TreatmentRelevant.PatSiteID) as NumberOfTreatments,
      dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
      FROM 
            dbo.view_3TreatmentRelevant
      GROUP BY
            dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel
) AS BaseData
PIVOT 
(
      MIN(BaseData.FirstTreatStart) FOR TreatmentDrugHighLevel IN (' + @Vals + ')      
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID'

/*Execute the dynamic string*/
EXEC ( @SQL )

/*SELECT DATA FROM YourPhysicalTableName*/
SELECT * FROM YourPhysicalTableName;

--

Open in new window

Mark WillsTopic AdvisorCommented:
All self contained in your stored procedure. We add an optional parameter @flush to tell it to flush / refresh 'Y' or not 'N'.

If not flushing / refresh, then use 'N' to display existing content

Now, we have used a file named TMP_PIVOT_RESULTS and you may want to change that to something more robust / unique.

And to run :


exec [proc_q3TreatmentRelevant_Crosstab] 'N'  -- No flush = display whatever is in the table, if it exists, if not exists then build it anyway.
or

exec [proc_q3TreatmentRelevant_Crosstab]    -- same as 'N'

or

exec [proc_q3TreatmentRelevant_Crosstab] 'Y'   -- to reset / flush by building a new table etc...




ALTER PROCEDURE [dbo].[proc_q3TreatmentRelevant_Crosstab] (@flush char(1) = 'N') 
AS
BEGIN

 set nocount on
 SET ANSI_WARNINGS OFF

 IF @flush = 'N' and object_id('tmp_pivot_results','U') is not null
 begin 
    select * from tmp_pivot_results
	return
 end

 -- print 'flushing'

 if object_id('tmp_pivot_results','U') is not null drop table tmp_pivot_results

/*Declare Variables*/
 DECLARE @vals AS VARCHAR(MAX) = ''
 DECLARE @Maxvals AS VARCHAR(MAX) = ''

/*Get names of dynamic columns*/
SELECT 
  @vals = @vals+ '['+ TreatmentDrugHighLevel +']' + ',' 
, @Maxvals = @Maxvals + 'MAX(['+ TreatmentDrugHighLevel +']) ' + '['+ TreatmentDrugHighLevel +']' + ',' 
FROM [dbo].[view_3TreatmentRelevant]
GROUP BY TreatmentDrugHighLevel

/*Remove last comma*/
SELECT @vals = SUBSTRING(@vals,1,LEN(@vals)-1)
SELECT @Maxvals = ',' + SUBSTRING(@Maxvals,1,len(@Maxvals)-1)

--PRINT @vals
--PRINT @MaxVals

/**Generate dynamic SQL*/
DECLARE @SQL AS VARCHAR(MAX) = '

SELECT 
      CoursePatId 
      ,MIN(FirstTreatStart1) FirstTreatStart
      ,SUM(NumberOfTreatments) NumberOfTreatments
      ' + @MaxVals + '
into tmp_pivot_results
FROM
(
      SELECT 
      dbo.view_3TreatmentRelevant.CoursePatID, 
      MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
      MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart1,
      COUNT(dbo.view_3TreatmentRelevant.PatSiteID) as NumberOfTreatments,
      dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
      FROM 
            dbo.view_3TreatmentRelevant
      GROUP BY
            dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel
) AS BaseData
PIVOT 
(
      MIN(BaseData.FirstTreatStart) FOR TreatmentDrugHighLevel IN (' + @Vals + ')      
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID

 select * from tmp_pivot_results
'

/*Execute the dynamic string*/
 EXEC ( @SQL )

END
GO

Open in new window



Now this was created using the latest version of SQL Server. The reason why it works is using the exec(@sql). You cannot normally create or delete then populate a table like that.

We should make it a lot more robust.

Cheers,
Mark Wills
ullenulleAuthor Commented:
Mark... that is like magic to me. ;-)  So cool!!!! Now... a little additional question:

1) If I want to run the SP via a website, how will the Y/N work?
2)  If I don't want that Y/N option but just launch the SP, how much shall I delete from the script?

Happy regards

Ulrich
Mark WillsTopic AdvisorCommented:
Ah, thats the beauty of the proc, you dont have supply any parameters :)

There shouldnt be anything that needs deleting. You could change the default to 'Y' so it always defaults to recalculate.

Now, I have not written it for concurrent access, so we might want to add another setting that indicates that the file creation bit should be totally ignored and the pivot simply works as before. Or tun the other one...

As for executing from a website - that is above my paygrade and would be a different question.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ullenulleAuthor Commented:
Thank you again. I'll open another question related to this  one. :-)
Mark WillsTopic AdvisorCommented:
My pleasure. Happy to have been able to help you.

Cheers,
Mark Wills
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.