copy a table

[camy123]

how do i make a copy of a table what i am trying to do is copy a table into a temp table.
the orginal table has 32 columns and i dont feel like recreating that manually as my temp table is there a way of copy that table so it has all the same column headeing ect when i display it as a temp table.

 

[LFCfan]

SELECT * INTO #temptable FROM MyTable

or SELECT TOP 0 * INTO #temptable FROM MyTable
if just want to copy the table definition but not the data

Another tip: if you're using SQL Server 2000, you can use the object browser to create INERT/UPDATE/SELECT statments without the need for typing, just right-click on the table in question and choose Script Object To...

HTH

 

[camy123]

Thanks sorry its monday morning.. should really wake up ..
cheers ..

 

[LFCfan]

get thee some coffee

 

[camy123]

yeh will do would u like one ehheheh plus shouldnt be talking to u im a spurs FAN
:-( how could you do it to us... ...

 

[camy123]

just one problem here..... I want to use the same temp table in a loop but when it loops pass it says.. #temptable already exist ne ideas on how to allow this... to be used again .

 

[LFCfan]

How do you mean used again? Created with a different structure? Or have rows inserted?

 

[camy123]

sorrt what i mean is have the same table open but allow more rows to be insert when i issue the statement
SELECT * INTO #temptable FROM MyTable
again it says the table already exists

for example
in my loop
it loops once and creates

SELECT * INTO #temptable FROM MyTable

but then on the second trip of the loop it says tables alreqdy exist .

 

[LFCfan]

The SELECT INTO statement can only be used when creating a new table

I'd put the SELECT INTO outside the loop, to create the temptable, and then just use INSERT INTO #temptable SELECT blah FROM MyTable in the normal way within the loop

 

[camy123]

oh yeh i could do a
If exists select * from temptbale #temptable (
isnert into blah blah blah

) else

select into 'temptable

cool.. thanks mate i hate mondays.. why cant we go straight on to tuesday ..