CODEWITHSUNDEEP
sql-servertable-variable
sharptooth
sharptooth

Reputation: 170569

How do I declare two table variables with identical structures?

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?

Upvotes: 9

Views: 5326

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

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

Upvotes: 4

Related Questions