Solved

What does 'Select * into" actually does

Posted on 2011-02-10
14
727 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 68

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now