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 (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – 2008 – Row Constructors – Load Temp Tables From Stored Procedures

  1. 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

  2. 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)???

  3. 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

  4. 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

  5. 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 ?

  6. 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!!!! :)

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s