How to declare two table variables with identical structures?

Question

I have the following table variable declaration:

DECLARE @MyTable TABLE ( --ten columns declared here ) 

and I want to declare another table variable with the same structure (so that I paste-from-select into the first, and then copy the result to the second, and then delete the records from the first variable one by one and return the second - as a result).

I tried this:

 DECLARE @MyTable, @MyTableCopy TABLE ( --ten columns declared here ) 

but SQL Server Express is not satisfied and says

Msg 102, Level 15, State 1, Line 1 Invalid syntax next to ','.

How to declare two identically structured table variables?

Answer 1

you cannot do this, however you can use the temp table to do this. A newly created #temp or parmanent table will have the same table structure.

 Declare @t table(startdate date,enddate date,duration int) select * into #t1 from @t select * from @t1 drop table #t1