How to copy structure of an existing table into a Table variable?

Posted by Peermohamedmydeen under Sql Server on 10/1/2010 | Points: 10 | Views : 5087 | Status : [Member] | Replies : 3
Hello All,

Nice Day,

I want to have a Table Variable, in which I want to have structure of a physical table.

How to declare a table variable with existing structure?


Eg:

Declare @tblTemp table() <-- Here I stuck with how to give structure of an existing physical table?

SELECT * INTO @tblTemp FROM tbl_PhysicalTable where 1=2 <-- This wont work for table variable, but works for Temporary Table.

And finally I want to do a insert into the Table variable like, with conditions and UNIONS.


INSERT @tblTemp (*)
(SELECT * FROM tbl_PhysicalTable WHERE nID= 1125
union
SELECT * FROM tbl_PhysicalTable1 WHERE nID= 1125)


Then Finally, I will have @tblTemp of filtered results.

How to achieve this?

Thanks in Advance.




Responses

Posted by: PandianS on: 10/2/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

1. Declaring a TableVariable from an Existing physical table "Is Not Possible". But we can create as Another physical table or Temporary table.

2. You have to declate the Table variable manually like...
DECLARE @tblTemp TABLE

(
ID INT, COLUMN1 VARCHAR(10)
)
3. OK. Once you created the table variable manually as given in Point#: 2

4. You can copy the results from an result set (Result set can be a collection of tables using JOIN, UNION, UNION ALL,...), But the result set should be single at a time as given below
INSERT @tblTemp

SELECT * FROM tbl_PhysicalTable WHERE nID= 1125
UNION
SELECT * FROM tbl_PhysicalTable1 WHERE nID= 1125
(OR)
INSERT @tblTemp

SELECT * FROM
(
SELECT * FROM tbl_PhysicalTable WHERE nID= 1125
UNION
SELECT * FROM tbl_PhysicalTable1 WHERE nID= 1125
) AS X
Limitation:
- The columns from a result set should be matched with target table or table variable(Number of columns) and Data type also should be compatible.

Cheers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Peermohamedmydeen, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Peermohamedmydeen on: 10/2/2010 [Member] Bronze | Points: 25

Up
0
Down
Hi Pandian,

Nice day,

Thank you very much for your response.

But I need the dynamism. When ever a new column added in the table, it should be updated in my result set.

Peermohamedmydeen, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 10/3/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Guys lets always put the code we post in the Code tags.

Thanks

Thank you for posting at Dotnetfunda
[Administrator]

Peermohamedmydeen, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response