SQL Select Into – Copying Made Easy

SQL – An Intuitive Way to Interact with Database

SQL has become very popular and is a universally acceptable database language. The benefit of SQL is that programmers and administrators need to learn one single language, and with minor adjustments, can apply it to a wide variety of database platforms, applications, and products. SQL is the standard language used to communicate with a relational database. You can use SQL for different database operations and one important function is to copy information from one table into another. SELECT INTO is the statement you use to copy data from one table and insert it into a new table.

Importance of Learning SQL

Every business has data and requires organizational methods or data management processes to maintain the data. Internet and high-speed, high-memory modern computers have brought a new perspective to the areas of data management and data handling. Modern databases with client/server configuration and web technologies are being used by businesses to effectively manage data and stay competitive in the market. So it is important to learn and understand SQL and how data structures are used to store information in an organization. SQL is the language you use to convey your needs to the database. You can request specific information from within a database by writing a query in SQL.

SELECT Statement and its Significance

SELECT statement represents Data Query Language (DQL) in SQL. A table is populated by using the INSERT statement. After this the SELECT statement is used in conjunction with the FROM clause to extract data from the database in an organized, readable manner. The SELECT keyword in a query is followed by a list of columns which you want to display as the result of a query output.

Apart from the keyword FROM, a SELECT statement can be followed by other keywords such as WHERE, ORDER BY to impose conditions with the SELECT statement.

Syntax for a SELECT Statement:

SELECT [column 1, column 2, …]

FROM source_table;

If you want to display all the columns of a table in the output, then use the symbol ‘*’ after SELECT.

SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a new table.

If we want to copy all columns of a table into a new table, then we use ‘*’ after the SELECT statement.

Syntax:

SELECT *

 INTO newtable [IN externaldb]

 FROM table1;

Example of SQL SELECT INTO Statement:

Here is an SQL SELECT INTO example to create a copy of the entire table of Vendors:

Syntax:

SELECT *

INTO Vendorcopy2014

FROM Vendors;

If we want to copy specific columns names into the new table then we enter:

Syntax:

SELECT [column 1, column 3, ….]

INTO newtable

FROM table1;

Here a new table will be created with the column-names and types as defined in the SELECT statement.

Example: To copy only the columns bearing Vendor Name and Country Name in the new table.

Syntax:

SELECT VendorName, CountryName

 INTO Vendorcopy2014

 FROM Vendors;

Example: Conditional copying of all vendor names only from the country Germany into the new table:

Syntax:

SELECT *

 INTO Vendorcopy2014

 FROM Vendors

 WHERE Country=’Germany’;

Limitations and Restrictions of SELECT and SELECT INTO

Here certain limitations and restrictions of a SELECT and SELECT….INTO statement have been discussed:

• SELECT…INTO statement cannot be used to create a partitioned table, even if the source table is partitioned. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO…SELECT FROM statement.
• Indexes, constraints, and triggers defined in the source table are not copied into the new table, nor can these restraints be specified in the SELECT…INTO statement. If these conditions are required, then you need to specify them after executing the SELECT…INTO statement.
• When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time the SELECT…INTO statement was executed.
• An aggregate-function column cannot be mixed with a non-aggregate column in a SELECT statement. The select list of a SELECT statement that has a GROUP BY clause can only have expressions from the GROUP BY clause or set functions. The use of an asterisk to select all columns in a SELECT statement containing a GROUP BY clause is not supported. Each of the names of the columns to be selected must be specified.
• To define column alias in a SELECT statement, the word “AS” must precede the alias. We use alias to replace a column heading with a shorter name, for example, “SELECT col 1 AS e from f.” Without “AS”, the statement will return an error.
• If an incorrect column name is entered into a SELECT statement, then the error “Wrong Number of Parameters,” is returned instead of “Column Not Found” error notice.
• When an empty string is inserted into a column, it is converted to NULL. In a WHERE clause, a searched SELECT statement executed on an empty string will not succeed on that column.
• The query ‘SELECT * INTO Table 1 FROM Table 2’ creates table 1 with the values from table 2. But if Table 1 already exists then the statement will give error.

Conclusion

If you want to explore the SELECT statement further and want to decipher the effects of the different clauses associated with the SELECT statement, then SQL Queries 101 will help you to understand how to write basic SQL queries and other code statements. As you hone your knowledge and skills to become a successful database administrator and developer, you need to start writing customized SQL code to ensure you meet the user requirements in the most efficient manner. If you want to explore Oracle SQL and Oracle PL/SQL Programming then taking a peek into the course such as Introduction to Oracle SQL, or Oracle PL/SQL Tutorial – A Comprehensive Training Course may be a good idea.