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 : 6620 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

Latest Posts