Copy table structure from a another one.

Question

Post reply

Copy table structure from a another one.

fasttrack

SSCertifiable

Points: 6071
More actions
June 6, 2005 at 4:02 am

#67096

Hi .....
I have to create only table structure without data:
I have used the syntiax:
SET @CreateTempTableSQL = 'Select * into #' + @TablesName + '_Temp from ' + @TablesName + ' where 1=0'
I have found out that using this format the table is create but if I want to save it as temp table, i put the prefix # but in temp DB It doesn't exist.
Are there another ways to create the table structure from another one and save it in temp DB.
With the method used above the copy is refered only to table field and datatype.
How can I copy the whole structure included triggers, PK and FK and so on.....
Thank a lot

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

What are you trying to accomplish? If you can provide that we may be able to help you better.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Jeff Gray SSChampion Points: 10667 More actions · Answer 2

Looks to me that all you have done is SET a variable to a value that contains an SQL statement that will copy the structure to a temporary table.

This will work, but you need to EXEC the statement for it to do anything.

EXEC sp_execustesql @CreateTempTableSQL

Once that statement executes, your new table will be in tempdb.

Note that the table will only be visible to the connection in which the table was created, so if you are doing this from QA, another window (connection) will not see the table.

Usually, temp tables are used within SPs or a script that uses the table for temporary storage. Once it runs, the table is discarded.

If you really want to put a table in the tempdb so that it will stay there, you may qualify the name as tempdb.dbo.TableName instead of #TableName. This will do what I think you are asking. Th table will persist until it is either dropped explicitly or the SQL service is restarted.

hth

JG

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

And I have found yet another way to misspell sp_executesql

😮

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

This also works...

select top 0 *

into #Temp

from Table

Signature is NULL

Chad Crawford SSC Enthusiast Points: 138 More actions · Answer 5

Your problem is going to be with the lifespan of a temp table - it only exists during the current session. So when you create a temporary table in an exec, it only exists during that exec (which wouldn't be too helpful).

For example, this will not work:

EXEC('CREATE TABLE #temp1 (col1 int)')

SELECT * FROM #temp1

However.... if you create the temporary table first, then reference it within an exec, it will work:

CREATE TABLE #temp1 (col1 int)

EXEC('INSERT INTO #temp1 VALUES(1)')

SELECT * FROM #temp1

Since you are dynamically creating the table (and my guess is you don't even know how many temp table you will need from run to run, I can think of two choices:

1)use real tables with some prefix like TMP, then delete them later

2)use global temporary tables (double pound ##TableName).

Both of these will have issues if your script is ever run twice simultaniously. If that's an issue, you might try adding something connection specific to the table name to keep them from crossing... something like EXEC('CREATE TABLE ##atemp1' + @@spid + ' (col1 int)')...

Good Luck!