What SQL Insert Into with a Select Statement Leads To?

sql select intoIntroduction

SQL or Structured Query Language is the standard language used to communicate with relational database management systems, such as Oracle, Microsoft SQL Server, Sybase, Informix, and so on. With SQL, you can build databases, enter data into the database, manipulate data, and query the database data. The output or results from the query are often used in analysis and also to make business decisions.

DML or Data Manipulation Language is the part of SQL that is used to make changes to data and tables in a relational database. With DML, user can populate tables with new data, update existing data, and delete data. Simple SQL database queries can be performed within a DML command. INSERT INTO is one of the three basic DML commands in SQL used to add one row to a table. But to populate a table with multiple rows we use the combination of an INSERT INTO and a SELECT statement.

SQL INSERT INTO Statement

In SQL, an INSERT INTO statement is used to insert or add one record or row to a table in a relational database.

Basic syntax of an SQL Insert INTO statement is:

INSERT INTO Table_Name

VALUES (‘value 1’, ‘value 2’, [NULL] );

The columns to be inserted in the table are specified in the VALUES list. The values inserted must be enclosed by single quotation marks for character and date/time data types. Single quotations are not required for numeric data types or NULL values. A value should be present for each column and the values should be in the same order as the columns to be listed in the table.

SQL SELECT Statement

SELECT statement represents Data Query Language (DQL) in SQL. After a table is populated by using the INSERT INTO statement, SELECT is used in conjunction with the FROM clause to extract data from the database in an organized, readable manner. The SEELCT keyword in a query is followed by a list of columns that you want to display as a 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 we want to copy all columns into the new table, then we use ‘*’ after the SELECT statement.

Syntax:

SELECT *

 INTO newtable

 FROM table1;

SQL SELECT INTO example to create a copy of the 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.

INSERT INTO SELECT Statement

A combination of INSERT and SELECT statement helps you to insert multiple rows and data into a table based on the results of a query from another table. A query is a question that the user asks the database and data returned is the answer to the question or query. The combination of INSERT INTO and SELECT statements allow you to conditionally, or unconditionally insert data into multiple tables. This statement reduces table scans and you have to write less code to perform multiple conditional inserts.

The basic syntax for inserting multiple records using an INSERT and SELECT statement is:

INSERT INTO table (column1, column2, … )

SELECT expression1, expression2, …

FROM source_table;

WHERE conditions;

Explanation of the parameters or arguments used in the above syntax:

  • table is the table to insert the records into.
  • column1, column2 are the columns in the table to insert the values.
  • expression1, expression2 are the values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
  • source_table is the source table when inserting data from another table.
  • conditions are conditions that must be met for the records to be inserted.

When using INSERT…SELECT between a target and a source table, it is mandatory to ensure that the datatypes returned by the SELECT statement are compatible with the datatypes in the target table.

Example of INSERT INTO SELECT Statement

Let’s assume we have two tables – one containing vendor information and the other containing the vendor order details.

Table Vendor_Information

Column Name                                 Data Type

Vendor_Name                                      char(50)

Order                                                        float

Ord_Date                                               datetime

 

Table Order_Data

Column Name                                 Data Type

Vendor_Name                                  char(50)

Order                                                    float

Ord_Date                                            datetime

Product_ID                                        integer

Table Order_Data has detailed order information placed with a particular vendor, while table Vendor_Information keeps a summary of the order placed with each vendor on a daily basis.

To move data from Order_Data to Vendor_Information, we would type in:

Syntax:

INSERT INTO Vendor_Information (Vendor_Name, Order, Ord_Date)

SELECT Vendor_Name, SUM(Order), Ord_Date

FROM Order_Data

GROUP BY Vendor_Name, Ord_Date;

After the INSERT INTO statement we specified the order of the columns to be displayed in the output table, for example, the first column should be Vendor_Name, second column Order, and the third column Ord_Date.

When do INSERT Statements Fail?

Some of the most common errors which occur while executing INSERT statement:

  • There is a mismatch between the number of columns and the number of values
  • If the datatype of an inserted value is a mismatch with the column of the target table
  • There is a size mismatch between a value and its target column.
  • When a column is defines as not NULL and the insertion value is NULL.

There are a large number of SQL enhancements in Oracle 9i. Multitable inserts are possible here, where

Conclusion

SQL is a simple, friendly, English-like language that is relatively easy to learn and is being extensively used by database users and developers all over the world. To be a successful DBA you have to know how to structure and develop a properly designed and linked database, which will help you to retrieve information from as many tables as you want. Courses like SQL Server Essentials: What you should know!  and Practical SQL Skills from Beginner to Intermediate will help you learn key SQL skills to boost your career.