?
Solved

What does 'Select * into" actually does

Posted on 2011-02-10
14
Medium Priority
?
735 Views
Last Modified: 2012-05-11
What does "SELECT * INTO " do?
0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +3
14 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 748 total points
ID: 34866184
This is normally an Access statrmrnt and is the "Make table" command. SELECT INTO MyTable will create MyTable and insert the records. If MyTable exists and DoCmd.SetWarnings False is set, then the exiisting table will be overwritten

Kelvin
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 34866194
Duh, just noticed this is a SQL 2005 question. Not sure if behaves the same there
0
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 504 total points
ID: 34866204
And its pretty much the same for SQL Server as well. You need to name your table as a temporary table. And the order by clause isn't honoured in SQL Server.

select * into #Temp from MyTable
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 18

Assisted Solution

by:Anil Golamari
Anil Golamari earned 252 total points
ID: 34866259
Select into will be used to create a backup for your tables (Mostly). This can be used to copy a data of a table from one table to other table. And you can also use IN to copy it to other database.

SELECT *
INTO Backuptablename  IN 'backupdatabase''
FROM tablename

SELECT *
INTO table2
FROM table1

SELECT *
INTO Backuptablename
FROM tablename

0
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 504 total points
ID: 34866354
I would disagree with "Select into will be used to create a backup for your tables (Mostly)" - I use it all the time for creating a temporary table of data especially in stored procedures and triggers where I need to serialise operations etc. Or any other time when a need to temporarily store a result set.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 34866393
OK, so it is the same as Access. It creates a table of the data your selected - for whatever prupose needed.

Kelvin
0
 

Author Comment

by:rayluvs
ID: 34866448
Ok... I understand it creates a table automatically... will it create also the origin table's definition, keys etc.?

0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 748 total points
ID: 34866475
Not necesarily, as the source may be a select statement based on a number of tables (i.e. a view). You may well have to follow with an ALTER TABLE statement to do this
0
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 248 total points
ID: 34866574
"Not necessarily" doesn't do it justice - never is correct. A select into just creates a table with columns having datatypes resulting from expressions. No metadata is copied, in particular NOT NULL/NULL, DEFAULT, Identity aso. And no index or keys. Imagine what would happen if you write:
   select 'one' as One, obj.* into newtmp from sysobjects obj;
0
 

Author Comment

by:rayluvs
ID: 34866713
Thats importane info... "SELCT INFO" does not copy table structure.
0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 748 total points
ID: 34866731
It just creates in a new table datatypes of the same type as the the source field (and data)
0
 
LVL 14

Assisted Solution

by:ldunscombe
ldunscombe earned 248 total points
ID: 34867573
If you need to maintain the table structure, indexes etc I generally do it this way.

Create a table with a make table query.
Set all the data types, indexes etc how I need them

And then instead of using a make table query I use a combination of a Delete and append queries.
ie Instead of re-creating the table each time simply Delete the existing Data and Append the New.

This is not suitable in ALL situations, but something that may help.

Leigh
0
 

Author Comment

by:rayluvs
ID: 34867708
Thanx to all
0
 

Author Closing Comment

by:rayluvs
ID: 34867732
Thanx
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question