Creating Table with Data from Stored Procedure

Question

How do I write the output of the following stored procedure to a table which does not exist and I would like to name that table as "MyNewTable"?

CREATE PROCEDURE GetProductDesc
AS
BEGIN
SET NOCOUNT ON

SELECT P.ProductID,P.ProductName,PD.ProductDescription, P.A, P.B, P.C, P.D, P.E, P.F, P.G, P.H, P.EYE, P.J, P.K, P.L, PD.AA, PD.BB, PD.CC, PD.EE  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID

END

Answer 1

Check a possible preliminary approach:

declare @q as varchar(max) = '
drop table if exists MyNewTable 
create table MyNewTable (' + 
(select STRING_AGG(QUOTENAME(name) + ' ' + system_type_name + ' NULL' , ', ') within group (order by column_ordinal) 
from sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('GetProductDesc'), 0)
where is_hidden = 0) +
')'

exec (@q)

insert MyNewTable
exec dbo.GetProductDesc

--

select * from MyNewTable

Answer 2

Msg 195, Level 15, State 10, Line 4
'STRING_AGG' is not a recognized built-in function name.
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@q".

I have
Microsoft SQL Server 2016
MSMQ 18.2

Answer 3

With new crucial details regarding the SQL version, if you have time, maybe try finding an equivalent for STRING_AGG like this one:

declare @q as varchar(max) = '
 drop table if exists MyNewTable 
 create table MyNewTable (' + 
 (stuff((select ', ' + QUOTENAME(name) + ' ' + system_type_name + ' NULL'
    from sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('GetProductDesc'), 0)
    where is_hidden = 0 for xml path('')), 1, 2, '')) +
 ')'

 exec (@q)

 insert MyNewTable
 exec dbo.GetProductDesc

 --

 select * from MyNewTable

By the way, if the procedure is converted to view, then you probably can use a simpler SELECT … INTO.

Answer 4

If the column ProductID is unique, try this:

INSERT INTO MyNewTable
SELECT * 
FROM OPENROWSET (
    'SQLNCLI','Server=(SERVER_NAME);Trusted_Connection=yes;', 
    'SET FMTONLY OFF; SET NOCOUNT ON; EXEC [DB_NAME].[dbo].[GetProductDesc]'
) AS Temp
WHERE [ProductID] NOT IN (SELECT [ProductID] FROM MyNewTable);

Answer 5

Hi @Qamar, Mo ，

Thank you so much for posting here.

The easiest way is to define the MyNewTable table and insert the result of procedure into this table.

CREATE TABLE MyNewTable (...);  
  
INSERT INTO MyNewTable  
EXEC GetProductDesc;  

If you would like not to define this new table manually, you could refer dynamic way provided by Viorel.

In addition, you could also use OPENROWSET method mentioned by Guoxiong.

I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

sp_configure 'Show Advanced Options', 1  
GO  
RECONFIGURE  
GO  
sp_configure 'Ad Hoc Distributed Queries', 1  
GO  
RECONFIGURE  
GO  
  
SELECT  
  *  
INTO  
  MyNewTable  
FROM  
  OPENROWSET (  
    'SQLNCLI',  
    'Server=localhost;Trusted_Connection=yes;',  
    'SET FMTONLY OFF;EXEC [dbname].dbo.GetProductDesc;'  
  );  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet