How to create a temp table in a procedure rather than usinh INTO #temptable

  • Hello Please.,

    I am having a quick question to automate something,

    we have 10 procedures in Sql server 2008 R, each procedure having between 2000 lines to 3000 lines

    in all procedures the temp tables used,

    for example,

    Select a.*, b.col1, c.col1, Case when d.col=1 then 'Match' else 'No Match' end as NewColumn

    INTO #myTemp1

    from

    Table1 a join Table2 B on a.col1=b.col1 join Table3 C on a.col1 = c.col2 join Table4 D on b.col1=d.col3

    So now the question is we have to change all procedures to create a temp table first then do insert into

    So with above example

    create table #Mytemp1

    (Col1 Varchar(20) NULL,

    Col2 Varchar(2) NULL,

    Co3 Date

    )

    Insert into #Mytemp1

    Select a.*, b.col1, c.col1, Case when d.col=1 then 'Match' else 'No Match' end as NewColumn

    ------INTO #myTemp1 Done want to be happen this

    from

    Table1 a join Table2 B on a.col1=b.col1 join Table3 C on a.col1 = c.col2 join Table4 D on b.col1=d.col3

    to do this it is taking big amount of time. also column lengths etc making some issues

    so my request to you is.

    is there anyway can i do this automatically creation of procedures etc.,

    could somebody please help me with this

    Thanks a lot in advance

    Asiti

  • Could somebody please help me, any ideas please...

  • asita (4/10/2014)


    Could somebody please help me, any ideas please...

    First, there's no way that I know of to do this and any even educated guess at calculated column widths could easily be wrong. That's one of the beauties of SELECT/INTO... you just don't need to know the column widths.

    So now the question is we have to change all procedures to create a temp table first then do insert into

    WHY? What does someone think this is going to do for you? In (if I dare say) most cases, this is just going to slow your code down.

    --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)

  • WHY? What does someone think this is going to do for you? In (if I dare say) most cases, this is just going to slow your code down

    Are there any advantages to creating the table then inserting the data? I've always used

    select

    x

    into #Mytable

    from dbo.othertables

    but many articles about temp tables seem to go down the create route. It seems you've got more flexibility with what you do to a temp table if you create it first, but is there actually any need?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Easiest way with accuracy to me is to run the code for each table create without producing any data, then script out the CREATE TABLE statement to copy into the proc. For example:

    Select

    a.*, b.col1, c.col1, Case when d.col=1 then 'Match' else 'No Match' end as NewColumn

    INTO tempdb.dbo.myTemp1 --must use a "real" table to script it out

    from Table1 a join

    Table2 B on a.col1=b.col1 join

    Table3 C on a.col1 = c.col2 join

    Table4 D on b.col1=d.col3

    where 1 = 0 --add this to prevent any actual rows from being generated

    After running that Select, in SSMS, under "tempdb", "Tables", right-click on "dbo.myTemp1" and do "Script Table as", "CREATE To", "New Query Editor Window". Whew. Then copy the "CREATE TABLE" text into the proc and change the table name to your original temp name.

    You can then add index(es) or whatever else is needed.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • One advantage is that Select...Into can lock the database until the insertion is completed.

    FWIW I sometimes go with this:

    -- Step 1. Create target table

    select <whatever>

    into #target

    where 1 = 0

    ...

    -- Step 2. Populate target table

    Insert into #target

    <whatever>

    This can be handy if you have a "template" table you can use for the first step and you want to populate the target table from a stored proc as in

    Insert into #target

    exec <stored proc that returns a table>

  • Gerald.Britton 28181 (4/11/2014)


    One advantage is that Select...Into can lock the database until the insertion is completed.

    Across linked servers, yes, and it does put certain shared locks on TempDB that make is so SSMS times out on TempDB in the explorer window IF you're trying to do something with TempDB there, but it hasn't locked up databases for normal usage since there was a hot fix way back in SQL Server 6.5.

    Don't take my word for it, though. Here's the MS document. Please notice the part where it states "NOTE: This problem does not apply to SQL Server 7.0 and later.".

    http://support.microsoft.com/kb/153441

    --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)

  • Well, the fix is not complete. I ran into this again in SQL Server 2008R2 just last year. I applied my workaround and voila! Lock gone.

  • gbritton1 (4/16/2014)


    Well, the fix is not complete. I ran into this again in SQL Server 2008R2 just last year. I applied my workaround and voila! Lock gone.

    Do you happen to remember what the code was doing during the SELECT/INTO?

    --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)

  • Jeff Moden (4/19/2014)


    gbritton1 (4/16/2014)


    Well, the fix is not complete. I ran into this again in SQL Server 2008R2 just last year. I applied my workaround and voila! Lock gone.

    Do you happen to remember what the code was doing during the SELECT/INTO?

    I don't remember it exactly. It was just a typical

    select ...

    into ...

    I replaced it with a two-part:

    select ...

    into ...

    where 1 = 0

    insert into ...

    select ...

    The difference was immediate, tangible and measurable. The first example prevented other queries with default transaction isolation levels from hitting the database until the insert was done. The second did not.

  • gbritton1 (4/21/2014)


    Jeff Moden (4/19/2014)


    gbritton1 (4/16/2014)


    Well, the fix is not complete. I ran into this again in SQL Server 2008R2 just last year. I applied my workaround and voila! Lock gone.

    Do you happen to remember what the code was doing during the SELECT/INTO?

    I don't remember it exactly. It was just a typical

    select ...

    into ...

    I replaced it with a two-part:

    select ...

    into ...

    where 1 = 0

    insert into ...

    select ...

    The difference was immediate, tangible and measurable. The first example prevented other queries with default transaction isolation levels from hitting the database until the insert was done. The second did not.

    Thank you very much for the feedback on that. I've never had that problem before. You certainly did the alternative the right way.

    --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)

Viewing 11 posts - 1 through 10 (of 10 total)

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