Solved

What does 'Select * into" actually does

Posted on 2011-02-10
14
728 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to incorporate a join with current sql query syntax 2 28
Query syntax 10 40
Need help subtracting a value within my script 7 42
Using OPENQUERY in a SELECT statement 6 34
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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 explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A short film showing how OnPage and Connectwise integration works.

914 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

20 Experts available now in Live!

Get 1:1 Help Now