Temporary table from stored procedure result in SQL Server

Can we create a temporary table from the stored procedure results dynamically?

I do not want to declare the temporary table columns manually. It shud take the schema of table from the stored procedure results.

  • SQL Combine multiple rows into one
  • SQL Server Partitioning - Unique Index Error
  • Find conflicted date intervals using SQL
  • Increase performance of SSIS Package dumping data from Excel to SQL Server table
  • SQL Server Express 4GB Limit
  • What is the proper way to insert millions of records from a csv file into a SQL Server database?
  • pyQT and MySQL or MSSQL Connectivity
  • SQL: sum differences in time until another column is a certain value
  • SQL Server : find duplicates in a table based on values in a single column
  • How to UPDATE only the columns that have NOT been previously updated in SQL SERVER
  • Many left joins on subqueries, need some way to increase performance
  • Selecting the second (middle) row between MIN & MAX values in SQL Server
  • 2 Solutions collect form web for “Temporary table from stored procedure result in SQL Server”

    use one of the Rowset Functions:

    SELECT  *
    INTO    #Temp
    FROM    OPENQUERY(SERVERNAME, 'EXEC pr_StorProcName')
    

    Use following syntax template to create temp table on basis of result set.

    Select * into #temptable from mytable
    
    
    Select column1,column2,..columnn into #temptable from mytable
    

    Notes:

    The SELECT INTO statement is very fast, for one reason: the command isn’t logged for backup purposes. More precisely, the command can be inside a transaction and any rollback command will correctly undo its effects. However, the new values aren’t permanently stored in the log file, therefore after this command you can only perform a complete database backup (incremental backup raise errors). This explains why you have to explicitly enable this functionality for non-temporary tables (temporary tables are never included in backup, so you don’t need to use the sp_dboption command before using SELECT INTO with a temporary table).

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.