Solved

What does 'Select * into" actually does

Posted on 2011-02-10
14
730 Views
Last Modified: 2012-05-11
What does "SELECT * INTO " do?
0
Comment
Question by:rayluvs
  • 5
  • 4
  • 2
  • +3
14 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 187 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 126 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 18

Assisted Solution

by:Anil Golamari
Anil Golamari earned 63 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 126 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 187 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 69

Assisted Solution

by:Qlemo
Qlemo earned 62 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 187 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 62 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…

840 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