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!
cdemott33Asked:
Who is Participating?
 
Atdhe NuhiuCommented:
You can, but I think you just want a union query

Select ID, PartName, PartID from table1
union
Select ID, PartName, PartID from table2
union
Select ID, PartName, PartID from table3
0
 
EvilPostItCommented:
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

0
 
EvilPostItCommented:
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.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
EL_BarbadoCommented:
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:

CREATE TABLE #PartsAll(
    Location VARCHAR(50),
    ID INT,
    PartName VARCHAR(50),
    PartID INT
)

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

SELECT Location, ID, PartName, PartID FROM  #PartsAll

-- Dont Forget to drop the table
DROP TABLE #PartsAll
0
 
cdemott33Author Commented:
Thank you all for your help.  Your answer put me on the right path.
0
 
Atdhe NuhiuCommented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.