create a temp table

I have a huge table with 20 columns. I only want to use 3 columns to create a temp table and then insert results into it.

I am pretty new to all this. The data structures have to match for insertion. So, how do I find the data types and then use specific columns to create table and then insert values into the table.

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.

Robert ShermanOwnerCommented:
You can do this with a "Make Table" query.   Create a new query in design mode.   Add the table you want the data to come from.   Change its type to "Make Table".  It will ask you for a name for the new table.   Enter it and select OK.  Then just add the fields you want to the query.  

When you run it, it will put the results into the new table.
Mike EghtebasDatabase and Application DeveloperCommented:
At this link, there is a table named SessionMap with many columns but I needed only a few columns:

See how this is handled using:
Create table #SessionMap(sessionMapKey int,	userKey	int, sessionKey int, [type] varchar(10));
Insert into #SessionMap(sessionMapKey,	userKey	, sessionKey , [type] ) values
(221354,	40183,	0,	'BW')
,(221353,	40183,	0,	'BW')
,(221352,	40183,	0,	'BW')
,(221351,	40183,	147666,	'DC')
,(221350,	32215,	0,	'BW')
,(221349,	27086,	0,	'SBW');

Open in new window

In your case you may have to use something like:
Create table #SessionMap(sessionMapKey int,	userKey	int, sessionKey int, [type] varchar(10));
Insert into #SessionMap(sessionMapKey,	userKey	, sessionKey , [type] ) Select col1, col2, col3, col4 From Table1;

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
>I only want to use 3 columns to create a temp table and then insert results into it.
Another possibility is to create a view that only uses the one table / three columns, and then insert into the view.

This is especially good if it's desired to only allow certain users/roles to perform this action, privs to the view can be granted to only those users/roles.

This is dependant on the other 17ish columns allowing NULL values, otherwise any attempt to insert only those three columns will fail.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Robert ShermanOwnerCommented:
It's becoming clear from the answers so far that more information on what you are trying to accomplish might be helpful.

First, why are you creating this temporary table?    Do you really need to create a temporary table (and duplicate data) or can you use a query or view to limit what is accessible?  Further, since views have been brought up, and your question is tagged with MS Access AND SQL Server, exactly which of the those two is your data stored in.    

I think Jim's answer leans towards SQL Server, and is the way to go if your goal is just to limit what fields can be accessed  by certain users.. however, since that solution is inserting into the original table, it brings up my earlier questions about the temporary table.

EDIT: Topic tags also include MySQL, so the plot thickens...
Jeffrey CoachmanMIS LiasonCommented:
Yes, why add records to a temp table?

Please describe your current configuration and your ultimate goal.

When you frame the question in terms of what you want to do, (create temp tables and add records)
...then it prohibits us from suggesting a more efficient technique.
Dale FyeOwner, Developing Solutions LLCCommented:
I have an article on creating and using temporary tables which includes some code that will allow you to automatically create the temp table in an external (not your currentdb) database and link it back to your currentdb.

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
Mike EghtebasDatabase and Application DeveloperCommented:
Also, you can use something like this. In this example from Table1 you want to create a table (in database or temp table). Table1 has many columns but the new table needs to have only column f1, f2 and f3:
, f2
, f3
FROM Table1
INTO MyNewTable
FROM cte;

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
Note to self:  Add to article Top 10 Ways to Ask Better Questions 'I've answered once, so did five other people, and we're waiting for a response before commenting again'
Robert ShermanOwnerCommented:
LOL @Jim.   I was thinking "..must be a slow news day."
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 Access

From novice to tech pro — start learning today.