Post reply

Dynamically Create a #table from existing Table

  • October 6, 2003 at 11:59 am

    #106243

    I would I go about dynamically creating a temp table from a table that exists?

    I want to create a table called #MonthlyStuff from the table called MonthlyStuff.

    Any ideas?

    Thanks from any help you can give me.

  • October 6, 2003 at 12:02 pm

    #477238

    select * into #MonthlyStuff from MonthlyStuff

  • October 6, 2003 at 12:05 pm

    #477239

    I want to CREATE a table from the existing table

  • October 6, 2003 at 12:12 pm

    #477242

    The statement will create temp table with same structue as original table and with data. If you just need table's defintion, truncate the temp table.

    Edited by - allen_cui on 10/06/2003 12:12:33 PM

  • October 6, 2003 at 12:24 pm

    #477250

    Thanks!! Is there a way to create the table without the data?

  • October 6, 2003 at 12:30 pm

    #477251

    quote:

    Thanks!! Is there a way to create the table without the data?

    

    SELECT *

    INTO #MonthlyStuff

    FROM MonthlyStuff

    WHERE 1 = 2

    --Jonathan



    --Jonathan

  • October 6, 2003 at 12:31 pm

    #477252

    I when an figured it out.

    select top 0 * into #MonthlyStuff from MonthlyStuff. Works Great.

  • October 7, 2003 at 6:33 am

    #477352

    Using a method like this to create a temporary table of zero rows has the added benefit of not locking up the source table while creating the temporary table. Particularly useful if the source table is very large or heavily used.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • October 7, 2003 at 7:09 am

    #477356

    I using it to process a single months data in DTS package. I get my data from a mainframe, I create the table, run a series of SQL statements (calculate cols from business data), clean up some nasty stuff the mainframe produces and take the result and put it into a history table. Very fast, much better than doing all this to the history table. Too many people running analysis in the history. Reduced my processing time from 20 minutes to about 6 minutes. I am loving it.

Viewing 9 posts - 1 through 8 (of 8 total)

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