To visit my site

HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#


To contact us

© 2002 - 2015 All Rights Reserved Total Application Works



  • Tell a friend about this site (copy and paste the following HTML page into a file (e.g., using Notepad).)
  • <html>
    <head>
    <title>Example of a link </title>
    </head>
    <body>
    <a href="http://sumtotalz.com/TotalAppsWorks/SQL/MY_SQL_INTO.html">
    Lists and their Tags </a>
    </table>
    </body>
    </html>




SQL Select INTO Command

Objectives

The objective of the lesson is as follows:

  • Describe a SQL SELECT INTO command
  • Select and list all rows and columns from a table to be copied
  • Select and list selected columns from a table to be copied
  • Select and list columns from multiple tables to be copied

Introduction

Computer users have may reasons for making copies of files or the partial contents of a file. We often use the cut-and-paste method to copy partial contents of a file. For example, if I have an algorithm that is tested and can be used with partial modifications in another application, I will probably cut-and-paste that algorithm and insert it in the source code of the application under development. There are similar needs for databases. The rest of this tutorial will explore methods of copying parts and/or full databases.

SELECT INTO

In DOS, the Copy command has a source and a destination, where the command looks like

  
Copy source destination
  

The DOS Copy command is used to copy a file.

The SQL SELECT INTO and the DOS Copy commands have similar functionality. With the SELECT INTO, the format is similar to the following.

  
SELECT what INTO  destination source
  

In this case,
  • what - are the columns to be copied
  • destination - the new database
  • source - the original database
The purpose of the SQL SELECT INTO command is to select data/columns from a SQL database table and copy these columns into another database, which has just been created. If we want to make a copy of the entire AccountName table we would issue the following SQL command.
  
SELECT * INTO AccountNameBAK FROM AccountName
  

In this case,
  • * - indicates that all of the columns are to be copied
  • AccountNameBAK - the new database
  • AccountName - the original database

How does this command work? Does SQL copy the rows out of the original table first? This would imply that SQL creates a temporary table to store the data and then moves the data to the final table. This approach implies a wasted operation. What SQL does is create the destination table and then it copies the specified columns from the original table into the destination table. If this last step fails for any reason, the destination table will be created but it will have no data. It will be an empty table. In our example the resulting AccountNameBAK table looks like:

AccountNo FirstName LastName
1234567890 James Wilie
1444567890 Julie Smith
1333567890 Mary Easley
1255567890 Cato Willingham
1236667890 Mike Jordan
1234777890 Carole Fusemi
1234567900 Peter Wie
1234000890 Donald Hill
1222567890 Regina Bell
1238888890 Althea Keyes
1234599999 Charles Lawrence
1233267890 Robert Urengo
1989867890 John Wieland
1212127890 Keith Makey

If you only want to copy some of the columns but not all, you can do so by listing them after the SELECT statement:
  
SELECT AccountName.FirstName, AccountName.LastName INTO AccountNameBAK 
      FROM AccountName
  

The resulting AccountNameBAK will look like:

FirstName LastName
James Wilie
Julie Smith
Mary Easley
Cato Willingham
Mike Jordan
Carole Fusemi
Peter Wie
Donald Hill
Regina Bell
Althea Keyes
Charles Lawrence
Robert Urengo
John Wieland
Keith Makey












[
HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#

© 2002 - 2015 All Rights Reserved Total Application Works