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

Question

0.00/5 (No votes)

See more:

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.

Thanks and Regards
PMM :)

Posted 1-Oct-10 17:31pm

PeerMohamedMydeen

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Goutam Patra · Answer 1 · 2010-10-01T19:49:00

Solution 1

CREATE TYPE mytableType AS TABLE( MyField1 VARCHAR(50), MyField2 INT )
DECLARE @MyVarTable AS mytableType
INSERT INTO @MyVarTable ....

Take a look at Here[^]

Posted 1-Oct-10 19:49pm

Goutam Patra

Comments

PeerMohamedMydeen 2-Oct-10 5:37am

Hi,
You are creating a type.
But, How can I have the structure of the existing physical table?