create table in stored procedure 1

[smsinger3]

Hello all. This might sound weird, but I would like to create a temporary table used in a stored procedure without using the typical "CREATE TABLE" to define all the columns. I would like to "copy" the structure for another table without me specifying the individual fields and keys. How can I do that instead of scripting the table and copying it to my stored procedure? It would be really cool if there was something like this:

CREATE TABLE #MyTempTable (COPY STRUCTURE FROM TABLE1)

Is there something like this?

Thanks for your help in advance.

Steve S.
sms@hmbnet.com

 

[Bernadette]

Hi There

It sounds like this is what you need ....
This script will create a new table and copy the definition from an existing table. The data will NOT be copied.


select * into new_table from existing_table
where 1=2


Hope This Helps :-> Bernadette

 

[smsinger3]

That works perfectly, but is there a way to also to create the index?

Thanks!

Steve

 

[sqlguru]

yes, you can try this..

create table #MyTempTable as select * from table1
or
create table #MyTempTable (select * from table1)

 

[Bernadette]

This method does not copy indexes or constrainsts. For that you will have to use the Create Table statement.

However if you are going to use it in a stored procedure you can get the defination by letting MS Sql generate a script for the table and copy and paste the schema it generates into your stored procedure ... it would save you from typing it all out if it is a big table. Make sure you include indexes and constraints in your script.

Hope This Helps :->

Bernadette