How do I 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 identical structure (so that I insert-from-select into the first one and then copy the result into the second one and then I delete entries from the first variable one by one and return the second one as a result).

I tried this:

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

but SQL Server Express is not happy and says

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','.

How do I declare two identically structured table variables?

Suttles

You can only do it without repeating the definition if you use a UDT - but that's a permanent change to the database. For ad-hoc tables, you've got to repeat the definition. – Modillion

Answer 1

you cannot do like that,however you can use temp table to do so.newly created #temp or parmanent table will have same table structure.

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

select * from @t1
drop table #t1

Hydrometallurgy