How to insert stored procedure data in temp table

Question

I have a stored procedure, from which i want data in temp table.

Is it possible to get data in table but is should drop and execute new data every time

I have tried using Select * from Openrowset(--Details-), But it is not working

Create PROCEDURE [dbo].[Auto Union Transaction]
AS
BEGIN 
DECLARE @DynamicTSQLStatement NVARCHAR(MAX);
SELECT @DynamicTSQLStatement = STUFF
(
    (
        SELECT N' UNION ALL SELECT * FROM ' + '[' + SCHEMA_NAME([schema_id]) + '].[' + [name] + ']'
        FROM [sys].[tables]
        WHERE [name] LIKE  'TRNS%9' or  [name] LIKE  'TRNS%20' 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,10
    ,''
);
EXEC sp_executesql @DynamicTSQLStatement

END

Data is one table

Possible duplicate of [Insert results of a stored procedure into a temporary table](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) — Eric Brandt, Jul 12 '19 at 20:13

score 2 · Answer 1 · answered Jul 11 '19 at 06:36

In order to insert the result set of a stored procedure into a table (the same applies for a temp table), you need to define your table structure first then call the procedure using an INSERT INTO ... EXEC statement.

Full documentation is at https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017 which also goes into the results sets options which apply more control over this.

However, I would suggest that given the nature of your procedure, your schema design is either flawed or there are much better ways of implementing this solution; perhaps a partitioned view, for example.

score 0 · Answer 2 · answered Jul 11 '19 at 07:10

Here you can see the answer : How to insert stored procedure data in temp table

Create PROCEDURE [dbo].[Auto Union Transaction]
AS
BEGIN 
DECLARE @DynamicTSQLStatement NVARCHAR(MAX);
SELECT @DynamicTSQLStatement = STUFF
(
    (
        SELECT N' UNION ALL SELECT * FROM ' + '[' + SCHEMA_NAME([schema_id]) + '].[' + [name] + ']'
        FROM [sys].[tables]
        WHERE [name] LIKE  'TRNS%9' or  [name] LIKE  'TRNS%20' 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,10
    ,''
);
-- Create temporary table here

CREATE TABLE #tmpTable
(
   COL1 Nvarchar(Max),  -- Change Max to number you want
   COL2 INT,
   COL3 INT,
   COL4 Nvarchar(Max)
)

INSERT INTO #tmpTable
EXEC sp_executesql @DynamicTSQLStatement

END

I hope this help you solve your problem.

How to insert stored procedure data in temp table

2 Answers2