SQL: 3 Inserting Methods

Introduction

This article will discuss 3 different methods in SQL to insert records either from input data or bulk copied from another table.

The content of the article:

Introduction
Definition
- SQL INSERT INTO Statement
- SQL INSERT INTO SELECT Statement
- SQL SELECT INTO Statement
Demos
- Test Data
- Demo 1: Copy data from one table into a new table.
- Demo 2: Copy data from one table into an existing table.
- Demo 3: Insert new records in a (existing) table.

Definition

There are 3 Inserting Methods in SQL:

SQL INSERT INTO Statement
SQL INSERT INTO SELECT Statement
SQL SELECT INTO Statement

SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a (existing) table.
It is possible in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

SQL INSERT INTO SELECT Statement:

The INSERT INTO SELECT statement copies data from one table and inserts it into another (existing) table.
The INSERT INTO SELECT the Statement requires that the data types in the source and target tables match.
The existing records in the target table are unaffected.
It is possible in two ways:

1. Copy all columns from one table to another table:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

2. Copy only some columns from one table into another table:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

SQL SELECT INTO Statement:

The SELECT INTO statement copies data from one table into a new table.
It is possible in two ways:

1. Copy all columns into a new table:

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

2. Copy only some columns into a new table:

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.

Demos

We use a very simple sample to demo the inserting situations above.

Test Data:

We use the Jobs Table in the Pub database. It is a simple table with only 4 columns:

The data inside the Table:

Demo 1:

Use both SELECT INTO Statement and INSERT INTO SELECT Statement to copy data from one table into a new table:

INSERT INTO SELECT Statement --- failed (you may even see the failure before run the script, after INTO, the table name is in red errors are highlighted):

SELECT INTO Statement --- successful (in the SQL script, the target table name is in no error status),

The new table is created with records copied:

Demo 2

Use both SELECT INTO Statement and INSERT INTO SELECT Statement to copy data from one table into another existing table:

SELECT INTO Statement --- failed because there is already a table existing there:

INSERT INTO SELECT Statement --- not successful due to the original table has an identity auto number that is not copyable.:

INSERT INTO SELECT Statement --- successful if we choose Copy only some columns from one table into another table:

The data from the original table is copied to the target table without affecting the data in the targeting able:

Demo 3

Use INSERT INTO Statement to insert records:

Three records are added:

References