Sql 简明教程
SQL - Insert Into… Select Statement
The Insert Into… Select Statement
SQL INSERT INTO… SELECT 语句用于将现有表中的一个或多个新行添加/插入到另一个表。此语句是两个不同语句的组合：INSERT INTO 和 SELECT。
The SQL INSERT INTO… SELECT statement is used to add/insert one or more new rows from an existing table to another table. This statement is a combination of two different statements: INSERT INTO and SELECT.
-
The INSERT INTO statement is one of the most fundamental and frequently used statements in database management and requires only the name of the table and the values to be inserted. However, it is important to ensure that the data being inserted satisfies the constraints if the columns of a table (if any) and its type matches the data types of the table columns.
-
The SELECT statement is used to retrieve data from an existing database table.
当这些语句一起使用时，SELECT 语句首先从现有表中检索数据，INSERT INTO 语句将检索到的数据插入到另一个表中（如果它们具有相同的表结构）。
When these statements are used together, the SELECT statement first retrieves the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures).
Syntax
以下是 SQL INSERT INTO… SELECT 语句的语法 −
Following is the syntax of the SQL INSERT INTO… SELECT statement −
INSERT INTO table_new
SELECT (column1, column2, ...columnN)
FROM table_old;在使用此查询之前，我们必须确保：
Before using this query, we have to make sure that −
-
In the database where we are going to insert data, source and target tables already exist.
-
The structure of the source and target tables are same.
Example
假设我们已经创建了一个名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和工资等，如下所示：
Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc.., as shown below −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，使用如下所示的 INSERT 语句向此表中插入值：
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);该表将被创建为：
The table will be created as −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
使用与 CUSTOMERS 表相同的结构创建另一个名为 BUYERS 的表。
Create another table named BUYERS with same structure as the CUSTOMERS table.
CREATE TABLE BUYERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);以下查询将 CUSTOMERS 表中的所有记录复制到 BUYERS 中：
Following query copies all the records from the CUSTOMERS table to BUYERS −
INSERT INTO BUYERS SELECT * FROM CUSTOMERS;Verification
如果您使用 SELECT 语句验证 BUYERS 表中的内容如下：
If you verify the contents of the BUYERS table using the SELECT statement as −
SELECT * FROM BUYERS;该表将被创建为：
The table will be created as −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
SQL - Inserting Specific Records
有时我们只需要向另一个表中添加少量记录。这可以通过将 WHERE 子句与 SQL INSERT INTO… SELECT 语句一起使用来完成。
Sometimes we only need to add a small number of records to another table. This can be accomplished by using a WHERE clause along with the SQL INSERT INTO… SELECT statement.
Example
让我们使用 CREATE 语句创建一个名为 NAMESTARTSWITH_K 的表，其结构与 CUSTOMER 表相同：
Let us create a table named NAMESTARTSWITH_K with the same structure as the CUSTOMER table using the CREATE statement as −
CREATE TABLE NAMESTARTSWITH_K (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);以下查询将以字母 k 开头的客户的记录从 CUSTOMERS 表插入到 BUYERS 表中：
Following query inserts the records of the customers whose name starts with the letter k from the CUSTOMERS table to the BUYERS table −
INSERT INTO NAMESTARTSWITH_K
SELECT * FROM CUSTOMERS
WHERE NAME LIKE 'k%';Verification
以下是用于验证以上创建的表内容的 SELECT 语句：
Following is the SELECT statement to verify the contents of the above created table −
SELECT * FROM NAMESTARTSWITH_K;该表将被创建为：
The table will be created as −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
SQL - Inserting Top N Rows
LIMIT 子句过滤查询中的行数。您可以使用它来过滤应该添加到目标表中的前 N 条记录。
The LIMIT clause filters the number of rows from the query. You can use this to filter the top N records that should be added to the target table.
Example
但是，在继续之前，让我们使用以下语句截断 BUYERS 表中的所有行：
But, before proceeding further, let us truncate all rows in the BUYERS table using the following statement −
TRUNCATE TABLE BUYERS;以下查询将 CUSTOMERS 表中的前 3 条记录插入到 BUYERS 表中 −
Following query inserts the top 3 records from the CUSTOMERS table to the BUYERS table −
INSERT INTO BUYERS
SELECT * FROM CUSTOMERS
ORDER BY ID ASC LIMIT 3;