Is there a way to create a temporary table in MS Sql?

I'm not sure if this is possible but here's what I'd like to do.

I'll like to create a store procedure that takes data from 3 tables and put that data into a "temporary table".   Then I want to return the data from the temporary table?  

For example.  I have three tables called PartsCT, PartsNY, PartsNJ that each have columns named ID, PartName, PartID.  Can I take the data from all three of these tables and put it in a temporary table called "PartsALL" and then have my stored procedure return the PartsALL table?

Is this possible?  If so, can you provide and example of how I could do this?  Thanks!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Atdhe NuhiuCommented:
You can, but I think you just want a union query

Select ID, PartName, PartID from table1
Select ID, PartName, PartID from table2
Select ID, PartName, PartID from table3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
What he said, but just in case there are duplicates you may want to do a union all and maybe a location column too...

Select 'CT' AS Location,ID, PartName, PartID from PartsCT
union all
Select 'NY' AS Location,ID, PartName, PartID from PartsNY
union all
Select 'NJ' AS Location,ID, PartName, PartID from PartsNJ

Open in new window

Oh yeah, and for future reference, temporary tables are created the same way normal tables are created. You just have to name them #tablename or ##tablename. # - Only exists within the current user and ## exists to all users.
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

You can do a UNION like Andy said but make sure that you use UNION ALL instead of union in this case. Also, to answer your question you can make a temporary table if you want to and return the results from that table using the query below:

    Location VARCHAR(50),
    ID INT,
    PartName VARCHAR(50),
    PartID INT

INSERT INTO #PartsAll( Location, ID, PartName, PartID)
SELECT 'CT', ID, PartName, PartID FROM PartsCT
SELECT 'NY', ID, PartName, PartID FROM PartsNY
SELECT 'NJ', ID, PartName, PartID FROM PartsNJ

SELECT Location, ID, PartName, PartID FROM  #PartsAll

-- Dont Forget to drop the table
cdemott33Author Commented:
Thank you all for your help.  Your answer put me on the right path.
Atdhe NuhiuCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.