CODEWITHSUNDEEP
sqlsql-servert-sql
user9393635
user9393635

Reputation: 1429

is it possible to "clone" a table variable?

I have a table variable with about 20 columns. I'd like to essentially reuse a single table variable structure for 2 different result sets. The 2 result sets should be represented in different table variables so I can't reuse a single table variable. Therefore, I was wondering if there was a way to clone a single table variable for reuse. For example, something like this:

DECLARE @MyTableVar1 TABLE(
   Col1 INT,
   Col2 INT
}

DECLARE @MyTableVar2 TABLE = @MyTableVar1

I'd like to avoid creating duplicate SQL if I can reuse existing SQL.

Upvotes: 0

Views: 1576

Answers (4)

SQLHelp
SQLHelp

Reputation: 41

You can create one table variable and add type column in the table and use the type column in your queries to filter the data.

By this you are using one table to hold more than one type of data. Hope this helps.

declare @myTable table(
   Col1 INT null,
   Col2 INT null,
....
  Type INT NULL
}


insert into @myTable(...,type)
select ......,1

insert into @myTable(...,type)
select ......,2

select * from @myTable where type =1 
select * from @myTable where type =2

Upvotes: 0

RnP
RnP

Reputation: 400

You can create a user-defined table type which is typically meant for using table valued parameters for stored procedures. Once the type is created, you can use it as a type to declare any number of table variables just like built-in types. This comes closest to you requirement.

Ex:

CREATE TYPE MyTableType AS TABLE   
( COL1 int  
, COL2 int )

DECLARE @MyTableVar1 AS MyTableType
DECLARE @MyTableVar2 AS MyTableType

A few things to note with this solution

  1. MyTableType becomes a database level type. It is not local to a specific stored procedure.
  2. If you have to ever change the definition of the table, then you have to drop the code/sprocs using the TVP type, then recreate the table type with new definition and related sprocs. Typically this is a non-issue as the code and the type are created/recreated together.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93704

That is not possible, use temp table instead 

if object_id('tempdb..#MyTempTable1') is not null drop table #MyTempTable1
Create TABLE #MyTempTable1 (
   Col1 INT,
   Col2 INT
)

if object_id('tempdb..#MyTempTable2') is not null drop table #MyTempTable2
select * into #MyTempTable2 from #MyTempTable1

update :

As suggested by Eric in comment, if you are looking for just table schema and not the data inside the first table then 

select * into #MyTempTable2 from #MyTempTable1 where 1 = 0

Upvotes: 4

S3S
S3S

Reputation: 25112

You could use a temp table and select into... they perform better since their statistics are better.

create table #myTable(
   Col1 INT null,
   Col2 INT null
}

...

select * 
into #myTableTwo
from #myTable

Upvotes: 1

Related Questions