CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Dinesh Saxena
Dinesh Saxena

Reputation: 71

Declaring Table Variable using Existing Table Schema in Sql

I want to declare a Table Variable in my stored procedure using existing tables schema.

I have a Table, say TableA, which has about 30 columns. I want to declare a Table Variable using the same columns just as we declare a Temporary Table.

For instance, I can declare a Temporary Table using the schema like this: 

SELECT TOP 0 * INTO #Temp_TableA FROM TableA

Can I similarly declare a Table Variable???

Upvotes: 7

Views: 11761

Answers (3)

Subh
Subh

Reputation: 16

DECLARE A Table Variable having same as SCHEMA of your table first and then INSERT INTO syntax as mentioned by Megatron.

If you are planning to use inside a stored procedure, then use CTE and don't forget to mention ; befire CTE declareation and insert into CTE variable from your table.

Upvotes: 0

Roman Marusyk
Roman Marusyk

Reputation: 24569

From MSDN:
No, table variable is a variable as name suggests so you need to declare it before you can use it like all other T-SQL variables and you need to use INSERT INTO

DECLARE @MyTable TABLE(
ID INT NOT NULL,
Data varchar(30) NOT NULL
);
INSERT INTO @MyTable
SELECT ID, data
From <table>

You can also use a temporary table in your stored procedure. Just add to the beginning of stored procedure this code:

if object_id('tempdb..#TableA') is not null drop table #TableA

Upvotes: 1

CoOl
CoOl

Reputation: 2797

You should use a CTE for this purpose: 

; with CTE as (SELECT TOP 0* FROM TableA)
SELECT * FROM CTE

The only thing to remember is CTE can only be used in the next line after the initialization. So for example, the following won't work-

; with CTE as (SELECT TOP 0* FROM TableA)
SELECT * FROM TableA
SELECT * FROM CTE

because here CTE will become invalid.

Upvotes: 0

Related Questions