Post reply

Copying Data returned by a stored procedure....Help please!

  • December 3, 2007 at 9:42 am

    #76835

    Hi all,

    I have a stored procedure that has in it a SELECT statement. This statement returns values from as many as 4 tables after filetring data. I want to get the result set into a Temp Table and then work with this table. When i tried inserting the stored procedure's result into a Temp Table created on the fly, i get the following error

    Msg 263, Level 16, State 1, Line 1

    Must specify table to select from.

    Msg 1038, Level 15, State 5, Line 1

    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

    The SQL Statement that i used is as follows

    SELECT * INTO

    #TempTable

    EXEC get_RptStaticPoolCopy1 'AA'

    GO

    I also tried getting the results into a normal table but that didnt work either. Kindly let me know if there is any other info that i could provide. Thanks for your help

    Senthuran

  • December 3, 2007 at 11:14 am

    #756594

    Judging by the error message, I'd assume the proc you're calling has an unnamed column. Procs can have those. Try running the proc separately and see if one of the columns comes up that way.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 3, 2007 at 11:17 am

    #756596

    Thanks for the reply GSquared. I tried running the proc separately and it works fine. All the columns have proper names and nothing is left null.

  • December 3, 2007 at 3:48 pm

    #756708

    Can't be done using EXEC... temp table must already exist.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 3, 2007 at 3:49 pm

    #756710

    Ummm... maybe you can do it using OPENDATASOURCE or OPENROWSET...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 3, 2007 at 8:15 pm

    #756767

    Thanks Jeff...i'll try that.

  • December 4, 2007 at 1:55 am

    #756807

    Or May be you can put the values into temp variables and copy them into the temp table. This would also work

    Regards,
    Genie Cool

  • December 5, 2007 at 2:07 am

    #757252

    Declare a Table variable (INPUT/OUTPUT) and pass it into the store procedure(get_RptStaticPoolCopy1 ) .In the storeprocedure(get_RptStaticPoolCopy1 ) you can fill the table variable which you are getting as a parameter .After filling it return it into your calling procedure and fill it into temptable.

    Eg:

    EXEC get_RptStaticPoolCopy1 'AA',@TableVariable

    SELECT * INTO

    #TempTable

    FROM

    @TableVariable

  • December 5, 2007 at 6:12 am

    #757309

    Have you actually tried that and gotten it to work?

    Besides, if you're gonna take the time to define a table variable, you might just skip that step and define the Temp table instead.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 5, 2007 at 10:10 am

    #757390

    How about this:

    -- Create a dummy proc

    create procedure GetYears as

    select 1, 2005, '10/1/2005'

    union

    select 2, 2006, '10/1/2006'

    union

    select 3, 2007, '10/1/2007'

    go

    -- Create a temp table to receive the results of proc

    create table #Years

    (AssessmentPeriodID int,

    AssessmentYear int,

    EmployedAsOf datetime)

    -- Insert proc results into the temp table

    insert into #Years

    exec GetYears

    -- Display results from temp table

    select * from #Years

    Lisa

  • December 5, 2007 at 3:40 pm

    #757462

    Thanks Sugesh, Jeff , geniecool and LSadvantage.....i'll try ur solutions and let u know....Thanks a lot for ur time

  • December 5, 2007 at 10:59 pm

    #757517

    Table variable is faster than Temp Table

  • December 5, 2007 at 11:53 pm

    #757529

    No, Not true. Table variables are NOT faster than TempTables. Are you saying that because you think table variables live only in memory or because you think temp tables always cause recompiles?

    Temp tables live in memory, just like table variables... if either overrun memory, they both spool to TempDB. But don't take my word for it... pay particular attention to the 2nd bullet of A3 and all of A4 in the following...

    http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

    Tables variables also have the nasty habit of showing up in the execution plan as a single row and that what the optimizer works with.

    And, you can avoid midstream recompiles on set based batch jobs by creating the temp tables at the begining of the proc before any DML occurs, just like it says in Books Online.

    It's a myth that table variables are always faster than temp tables... sometimes they are and sometimes they're not... it usually depends on how many rows you're working with.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 6, 2007 at 12:56 am

    #757559

    Thanks for your information.

    I found, when working with Table variables it's faster than Temp table.

  • December 7, 2007 at 10:56 pm

    #758267

    Yeah, I've seen that... but usually for smaller sets of rows.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply