What does 'Select * into" actually does

What does "SELECT * INTO " do?
rayluvsAsked:
Who is Participating?
 
Kelvin SparksConnect With a Mentor Commented:
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
 
Kelvin SparksCommented:
Duh, just noticed this is a SQL 2005 question. Not sure if behaves the same there
0
 
Dale BurrellConnect With a Mentor DirectorCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Anil GolamariConnect With a Mentor Commented:
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
 
Dale BurrellConnect With a Mentor DirectorCommented:
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
 
Kelvin SparksCommented:
OK, so it is the same as Access. It creates a table of the data your selected - for whatever prupose needed.

Kelvin
0
 
rayluvsAuthor Commented:
Ok... I understand it creates a table automatically... will it create also the origin table's definition, keys etc.?

0
 
Kelvin SparksConnect With a Mentor Commented:
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
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
"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
 
rayluvsAuthor Commented:
Thats importane info... "SELCT INFO" does not copy table structure.
0
 
Kelvin SparksConnect With a Mentor Commented:
It just creates in a new table datatypes of the same type as the the source field (and data)
0
 
ldunscombeConnect With a Mentor Commented:
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
 
rayluvsAuthor Commented:
Thanx to all
0
 
rayluvsAuthor Commented:
Thanx
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.