-1

I want to insert the stored procedure result set into a table. Is it possible to create a table dynamically? Columns may vary so please help.

This is my stored procedure result set where columns BYL, NYX, PAR may vary to more than 10 columns.

I need to dynamically create a table and insert this data into that table.

I tried queries like

CREATE TABLE new_tbl BY 
    SELECT * 
    FROM tmrCount1;

But this are possible in MySQL not in SQL Server

Tol1    toolname    ABC DEF GHJ HFJ KJD
--------------------------------------------
1           test1       18  0   24  0   1
2           test2       0   1   0   0   0
4           test3       0   1   0   1   0
5           test4       14  0   60  1   2
pramod zirale
  • 1
  • 1
  • use [SELECT - INTO Clause](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-ver16) – Squirrel Dec 02 '22 at 05:13
  • 3
    Do you want to insert the results of the SP *outside* the SP? Or *inside* the SP? – Dale K Dec 02 '22 at 05:23
  • Is `GlobalFlowingCount1` a table or stored procedure? If it's a table you need `SELECT INTO`. If it's a stored procedure you can't use SELECT at all. The table must already exist even it it's temporary. After that you can use `INSERT INTO #tmpBus Exec SpGetRecords 'Params'` – Panagiotis Kanavos Dec 02 '22 at 08:15
  • Seems there's [an identical question](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) with 1700 upvotes. The answers explain all the options, including one that uses `OPENROWSET` to allow `SELECT INTO` – Panagiotis Kanavos Dec 02 '22 at 08:18
  • ok.... how can create temp as table column are dynamic.. but data type for table column is fix. it will character... – pramod zirale Dec 02 '22 at 08:28
  • Solved using Global table ##temp. thankyou all – pramod zirale Dec 03 '22 at 11:56

1 Answers1

-1

Much more simple in MS SQL Server :

SELECT * 
INTO new_tbl BY 
FROM GlobalFlowingCount1;
SQLpro
  • 3,994
  • 1
  • 6
  • 14