SQL SERVER – 2008 – Row Constructors – Load Temp Tables From Stored Procedures

While playing with SQL Server 2008 I found new feature of “Row Constructors”, where I can load temp table from stored procedure directly.

Look at the following SQL where I have to use OpenQuery from server to itself creating loopback server and execute stored procedure and insert into temp table.

INSERT INTO #TempTable
SELECT *
FROM OPENQUERY(ServerName, 'exec StoredProc')

Above mentioned same query can be now written with simpler statement as described here.

INSERT INTO #TempTable
EXEC StoredProc

Note that this does not work with real tables or any other objects. This feature is only available to load temp tables.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Surface Area Configuration Tools Reduce Exposure To Security Risks
Next Post
SQLAuthority News – My Favorite Link of This Blog

Related Posts

7 Comments. Leave new

  • What’s the differetnt from SQL Server 2005. This script works well in SQL Server 2005 and in SQL Server 2000.

    Also with ‘real’ tables.

    use master
    drop database testdb
    go
    create database testdb
    go
    use testdb
    create table t (i int)
    go
    insert into t values (1)
    insert into t values (2)
    insert into t values (3)
    insert into t values (4)
    go
    create proc usp_t
    as
    select *
    from t
    go
    select top 0 *
    into #t
    from t
    go
    insert into #t
    exec usp_t
    go
    select * from #t
    go
    drop table #t

    Reply
  • hi sir,
    can u pls help me??

    WITH TopRow
    AS
    (
    SELECT TOP 100 Applied_to_leavemanager,leavetype,leavefrom,leaveto,No_of_hours,status
    FROM ahd_leave
    ORDER BY leaveID
    )
    SELECT *
    FROM TopRow

    by using this queryy i can retrive the entire data but how can i retrieve this data row wise to my table(in my page)???

    Reply
  • In earlier version also, it is possible with Openrowset

    Reply
  • hi i need one help. i have created one procedure, in which i am retriving data from link server by using open query , the problem is i want to retrive the result after executive above procedure in a variable. can u help me pls irs very urgent

    create PROCEDURE usp_GetList @CorrectScore varchar(7500) output
    AS
    declare @QuesBankId nvarchar(3900)
    set @QuesBankId=’SFHISTORYQuestionbank01′
    set @CorrectScore = ‘Select SectionCorrectScore from UPSSF_History..tblTestSectGeneral_History where QuestionBankId=”’+ @QuesBankId +””
    set @CorrectScore = N’select * from openquery
    (ASHOKGPC,”’ + REPLACE(@CorrectScore, ””, ”””) + ”’)’
    GO

    Reply
  • Hi Pinal,

    I refered your code sample and tried to execute it, but it was not useful . I need to insert into temporary table from Stored Procedure result set. The problem is the scehma for the result set returned by the stored procedure is not known paramter. Hence please help me to get some code sample in order to resolve this issue.

    Thanks In Advance,
    Santosh

    Reply
  • wat if have row constructor within Store Procedure like this :

    insert into tblAcademicRecord(educationlevel, examname,institution,passingyear,obtainedmarks,totalmarks,grade, percentage, remarks, userid)
    values(@educlvl, @examname, @institution, @Passingyr, @obtainedmarks, @totalmarks, @grade, @percentage, @remarks, @userid),(@educlvl, @examname, @institution, @Passingyr, @obtainedmarks, @totalmarks, @grade, @percentage, @remarks, @userid)

    now how to pass values to it via EXEC ?

    Reply
  • Record Insertion in 3 Different Ways –
    (Using SQL SERVER 2008)

    —- Declare table variable ———-
    DECLARE @tblCountries table
    (id int, country varchar(50))

    ————————————————

    –Inserting multiple values
    Insert into @tblCountries (id,country)
    Values
    (1,’USA’), –Row 1
    (2,’UK’), –Row 2
    (3,’France’) –Row 3

    –Now select, you will get 3 rows
    Select * from @tblCountries
    ————————————————
    Insert into @tblCountries (id,country)
    SELECT 1,’China’
    UNION ALL
    SELECT 2,’Japan’
    UNION ALL
    SELECT 3,’Thailand’

    Select * from @tblCountries
    ———————————————–
    Insert into @tblCountries (id,country)
    EXEC [USP_Test]

    /*
    CREATE/ALTER PROCEDURE [USP_Test]

    AS
    — EXEC [USP_Test]
    BEGIN

    SET NOCOUNT ON;
    SELECT 1,’India’
    UNION ALL
    SELECT 2,’NY’
    UNION ALL
    SELECT 3,’London’
    SET NOCOUNT OFF;
    END
    GO
    */

    Select * from @tblCountries
    ———————————————–

    Njoy!!!! :)

    Reply

Leave a Reply

Menu