Post reply

INSERT INTO from stored procedure - help pls!

  • July 18, 2006 at 9:44 pm

    #112865

     

    Hi all,

    We all know that the results of a stored procedure can be added to a table with the statement

    INSERT INTO #Table EXEC SPName

    I have a problem where I need to pass the SP some variables so I go

    INSERT INTO #Table EXEC SPName @var1, @Var2    which works fine, I get the results I need into #Table BUT it gets tricky when I also need the variable values on the SAME ROW in #Table.

    I don't have the luxury of being able to edit the stored proc so I need those values to come from the INSERT INTO.

    Any ideas?

    Thx in advance

    Danster

  • July 18, 2006 at 11:23 pm

    #650010

    INSERT INTO #Table EXEC SPName @var1, @Var2

    Select @var1 as Var1, @Var2 as Var2, *

    INTO #Table2

    FROM #Table

    _____________
    Code for TallyGenerator

  • July 19, 2006 at 1:09 am

    #650018

    Ahhh.

    I see you're using the 2nd table trick.

    I was hoping to avoid that.

    Any other suggestions out there?

  • July 19, 2006 at 1:21 am

    #650020

    ALTER TABLE #Table

    ADD Variable1 <data type>

    ADD Variable2 <Data Type>

    UPDATE #Table

    SET Variable1 = @Var1, Variable2 = @Var2

    Is that what you're looking for?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2006 at 4:50 am

    #650050

    If you test both approaches for performance you'll find out that yours is slower.

    Relational databased are really bad on altering tables.

    _____________
    Code for TallyGenerator

  • July 19, 2006 at 5:08 am

    #650057

    I suggeested it as al alternative to a second table, which he said wasn't really what he was looking for.

    A second thought, no need to do the update, can specify the variables as defaults on the new columns (will need dynamic SQL though)

    I did a time test to see how much slower the alter was

    CREATE

    TABLE #test (

    id int,

    name sysname

    )

    INSERT

    INTO #test

    SELECT id, name FROM sysobjects

    -- 5546 rows

    GO

    SELECT

    getdate() --2006-07-19 13:00:00.927

    SELECT

    id, name, 1 AS var1

    INTO #test2

    FROM #test

    SELECT

    getdate() --2006-07-19 13:00:00.947

    ALTER

    TABLE #test

    ADD Var1 tinyint DEFAULT 1

    SELECT getdate() --2006-07-19 13:00:00.947

    DROP

    TABLE #test

    DROP TABLE #test2

    Net result: select into takes 20 ms, alter table takes 0. I ran a couple times and times are consistent.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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