[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

What does 'Select * into" actually does

Posted on 2011-02-10
14
Medium Priority
?
738 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 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 71

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

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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