Hello,
I'm trying to write records in a table from a stored procedure which queries out different number of fields each time when I run it at different time. So I'll need a table that cannot have fixed fields with certain data type.
I learned that there is a way to use "select * into TableA from exec Stored_Procedure" and this approach will create a table and write in the data from a stored procedure. The statement is:
Select * into [dbo].[Sum_CustomerInfo] from exec [dbo].[Calculation_from_Customer] ;
But, when I run it, I got the error: " Incorrect syntax near the keyword 'exec'."!
Do I miss something in the statement? Do I need exec some system stored procedure before I run this statement? Or do I need some special authentication to run this insert statement?
Thank you harishgg1 for your answer,
Do you mean that the way to insert record from stored procedure: Select * into Table from exec Stored_Procedure
is only working for SQL Server 2016 (and 2017)?
hopefully what i am explaining below makes sense
if not please let me know ... i can put diagrams and explain
hi what i mean is
it maynot be straightforward in earlier versions of SQLServer
example
in sql server 2005
to do something
its a issue .. lot of complicated code
you have to do 10 steps etc
microsoft development SQL Server division
looks into all these kind of things also
in sql server 2016
to do these 10 steps
they create a function or something
just type function name and 1 step very small piece of code
DONE ...
Thank you harishgg1,
Your explanation makes sense. Yes the SQL Server 2016 has some new feature and make things easier. But, can you tell me what the correct 1 step is in 2016? Also, I'm using SQL Server 2012. May be there is a 2-3 steps that makes the same thing to insert to a table with data from stored procedure in SQL 2012? Can you show me that if you know it?
Thank you Muj9,
I tried this and the error is:
Server 'DevServer1\Instance1' is not configured for DATA ACCESS.
How can I configure the server or add another parameter in the openquery( ) function to give me data access right?
Thank you harishgg1,
The issue is that I cannot create a table (no matter temp table or perm table) in advance as the stored procedure I created returns different numbers of fields and data type on each field when I run it each time.
That is the reason why I'm trying to use this approach.
Thank you harishgg1,
This with CTE won't insert the data from the stored procedure into any table, right?
But I'll try it to see if this with CTE as (exec sp) will get data from my stored procedure.
Thank you mannesravya for your reply,
I actually can't insert the data into a pre-defined table as each time my stored procedure may return 10 fields, 12 fields or 13 fields and the field name will also be changed. So, I must use an approach to insert the data into a table without pre-defined fields.
I tried many approaches without any luck. maybe the SQL 2016 will work on the select * into Table from exec sp, as this will create a table and insert whatever the stored procedure returns data into the table.
The problem is that you created a dynamic stored procedure that returns an unknown set of data. The question I have is why do you feel this is the correct approach?
What is that procedure doing and why can it return a different set of data depending on how it is called? This is almost never a good idea as it cannot be utilized by any downstream processes...which you are finding out now.
If the only difference in the columns returned are that certain parameters won't return a result - then you should still return the column without any value. Return the column regardless of the calculations being performed with either a NULL value - or some other value to denote that this columns value was not calculated.
Thank you jeffw8713 for the reply,
The reason that I’m using this approach is that the stored procedure creates a set of data with the field name of date. It is calculated by Pivot to display the Sum of prod sold by each month (could be week or day) like this:
So, the field number keeps changing on each month, (each week, each day not in the example)and the field name are also different on each month, week, day.
This is the first step to get the data calculated by the stored procedure and write to a table, The second step, I’ll use the Prod_Name the foreign key to join the Production table to get the prod description, price, size… populated after Prod_Name. As in the Pivot calculation I can’t have all other fields listed in the table without list them in the “group by” and “rollup” clause, I have to “add” these fields after the calculation. The third step is to create a view to display whole data in a report and drop the table created in the first step.
Next time, when the we run the report, these steps will be processed again. If this is not a good approach, please tell me a better one. But the first step can’t be done as is the biggest issue.
Thank you mannesravya, I tried this in my dev server and got error.
First I exec sp_configure 'Show Advanced Options', 1
and sp_configure 'Ad Hoc Distributed Queries', 1
Then I got server name: select @@servername.
Then, I run the insert statement: SELECT * INTO My_Testing_Table FROM OPENROWSET('SQLNCLI', 'Server= MyDevSRV01;Trusted_Connection=yes;', 'EXEC dbo.[YTD_Prod_Calculate]')
Then I got error: OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired". OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". Msg 65535, Level 16, State 1, Line 0 SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
This approach isn't going to work - since you are looking at creating a view from the table where you don't know what columns will be returned. Any downstream usage of that view will break as soon as you recreate the view with different columns.
If you are using SSRS for your reports - then utilize a matrix in that tool instead of trying to create a dynamic pivot. If the tool/utility you are using for the report does not have the capability of pivoting the data and totaling - then you should change your approach.
Create separate procedures for each type - yearly/quarterly/monthly/weekly/daily - define a fixed number of columns for each type with fixed column headers and then use substitution in the reporting layer to change the fixed column headers to external display values.
For example - if your monthly pivot is a rolling twelve months - you would have columns Month01, Month02, Month03 ... Month12. In the report you substitute the value Month01 with the first month of data name (ex: 2017-Nov for Month01, 2017-Dec for Month02, ...2018-Oct).
If the report is a monthly YTD report for a calendar year - then your fixed columns are: Jan, Feb, Mar, etc... without any date reference in the column header. You put those date references as header data in the report one time to show the date range...any months that are not populated will show as blank on the report, or you add a column for the year...
Just think what happens if someone runs this as a daily report - and before it finishes someone else kicks it off to run a monthly report.
Can you show me that if you know it?