Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQl Server : How to insert results of a stored procedure to a table?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 197
    Comment on it

    In the following article we will learn how to insert results of a stored procedure to a table. There are two approaches to do this depending on whether we know the table schema beforehand or not. We will be using the following stored procedure to illustrate the two approaches.

    CREATE PROCEDURE GetFileNames
    AS
    SELECT FileName, FileID
    FROM Files
    GO
    

    1) Table exist

    Since schema is already known we can use the below solution:

    CREATE TABLE #Files
    (
       [FileName] NVARCHAR(256), 
       [FileID] INT
    );
    
    INSERT INTO #Files
    EXEC GetFileNames
    
    SELECT * FROM #Files
    

    2) Table does not exist

    Since schema is not known we need to use the below solution:

    SELECT * INTO #Files FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;','EXEC tempdb.dbo.GetFileNames')
    SELECT * FROM #Files
    

    In case of error do check if adhoc queries are enabled. If not we can enable as below:

    sp_configure 'Show Advanced Options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE
    GO
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: