Sql 简明教程

SQL - Select Into Statement

The SQL Select Into Statement

SQL SELECT INTO 语句创建一个新表，并将现有表中的数据插入新创建的表中。新表是根据 SELECT 语句中列的结构自动创建的，可以在相同的数据库或不同的数据库中创建。

The SQL SELECT INTO Statement creates a new table and inserts data from an existing table into the newly created table. The new table is automatically created based on the structure of the columns in the SELECT statement and can be created in the same database or in a different database.

但是，值得注意的是，SELECT INTO 语句不保留原始表的任何索引、约束或其他属性，新表在默认情况下没有任何主键或外键。因此，如果需要，你可能需要手动将这些属性添加到新表中。

However, it’s important to note that the SELECT INTO statement does not preserve any indexes, constraints, or other properties of the original table, and the new table will not have any primary keys or foreign keys defined by default. Therefore, you may need to add these properties to the new table manually if necessary.

Syntax

以下是 SQL Server 中 SQL SELECT INTO 语句的基本语法 −

Following is the basic syntax of the SQL SELECT INTO statement in SQL Server −

SELECT * INTO new_table_name FROM existing_table_name

Example

让我们创建 CUSTOMERS 表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和工资等，如下所示 −

Let us create the CUSTOMERS table 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 );

CUSTOMERS 表将按如下方式创建 −

The CUSTOMERS table will be creates as follows −

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

以下 SELECT INTO 语句创建一个名为 CUSTOMER_BACKUP 的新表并将 CUSTOMERS 表中的数据复制到其中 −

The following SELECT INTO statement creates a new table called CUSTOMER_BACKUP and copies the data from the CUSTOMERS table into it −

SELECT * INTO CUSTOMER_BACKUP FROM CUSTOMERS;

Output

我们得到以下结果。我们可以观察到有 7 行已修改。

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

我们可以通过使用 SELECT 语句检索其内容来验证更改是否反映在表中。以下是显示 CUSTOMER_BACKUP 表中记录的查询 −

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_BACKUP table −

SELECT * from CUSTOMER_BACKUP;

显示的表如下 −

The table displayed is as follows −

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

Copying Data From Specific Columns

我们还可以使用 SQL SELECT INTO 语句将现有表中特定列的数据复制到新表中。为此，我们只需要在 select 关键字后包含所需的列名即可。

We can also copy data from specific columns from an existing table into the new table using the SQL SELECT INTO statement. To do so, we just need to include the required column names after the select keyword.

Syntax

以下是语法 -

Following is the syntax −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM existing_table_name;

Example

在以下查询中，我们使用 CUSTOMERS 表中的 NAME、AGE 和 ADDRESS 列创建一个名为 CUSTOMER_DETAILS 的新表，并用相应的数据填充它。

In the following query, we are creating a new table called CUSTOMER_DETAILS with only the NAME, AGE, and ADDRESS columns from the CUSTOMERS table, and populate it with the corresponding data.

SELECT name, age, address
INTO CUSTOMER_DETAILS
FROM CUSTOMERS;

Output

我们得到以下结果。我们可以观察到有 7 行已修改。

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

我们可以通过使用 SELECT 语句检索其内容来验证更改是否反映在表中。以下是显示 CUSTOMER_DETAILS 表中记录的查询 −

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_DETAILS table −

SELECT * from CUSTOMER_DETAILS;

显示的表如下 −

The table displayed is as follows −

NAME

AGE

ADDRESS

Ramesh

32

Ahmedabad

Khilan

25

Delhi

Kaushik

23

Kota

Chaitali

25

Mumbai

Hardik

27

Bhopal

Komal

22

Hyderabad

Muffy

24

Indore

Note: 新表不会包含原始表中的任何其他列。原始表也保持不变。

Note: The new table will not include any other columns from the original table. Also the original table remains unchanged.

Copying Data From Multiple Tables

使用 SQL SELECT INTO 语句，我们还可以将数据从多个表复制到一个新表中。这是通过使用 JOIN 子句完成的，它基于公共列组合来自多个表的数据。

Using the SQL SELECT INTO statement we can also copy data from multiple tables to a new table. This is accomplished using the JOIN clause which combines the data from multiple tables (based on a common column).

Syntax

以下是使用 SELECT INTO 语句从多个表复制数据的语法 −

Following is the syntax to copy data from multiple tables using the SELECT INTO statement −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM table1
JOIN table2 ON table1.column = table2.column

Example

首先，让我们创建一个名为 ORDERS 的另一个表 −

First of all, let us create another table named ORDERS −

CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2));

使用 INSERT 语句像下面这样向该表中插入值：

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

表创建如下 −

The table is created as −

OID

DATE

CUSTOMER_ID

AMOUNT

102

2009-10-08 00:00:00

3

3000.00

100

2009-10-08 00:00:00

3

1500.00

101

2009-11-20 00:00:00

2

1560.00

103

2008-05-20 00:00:00

4

2060.00

现在，我们正在创建一个名为 CUSTOMER_ORDERS 的新表，其中包含来自 CUSTOMERS 表的客户姓名以及来自 ORDERS 表的客户 ID，其中 CUSTOMERS 表中客户的 ID 与 ORDERS 表中客户的 ID 相匹配 −

Now, we are creating a new table called CUSTOMER_ORDERS that includes the customer name from the CUSTOMERS table and the customer id from the ORDERS table, where the id of customers from the CUSTOMERS table matches with the id of customers from the ORDERS table −

SELECT CUSTOMERS.Name, ORDERS.customer_id
INTO CUSTOMER_ORDERS
FROM CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.customer_id;

Output

我们得到以下结果。我们可以观察到有 8 行已修改。

We get the following result. We can observe that 8 rows have been modified.

(8 rows affected)

Verification

我们可以通过使用 SELECT 语句检索其内容来验证更改是否反映在表中。以下是显示 CUSTOMER_ORDERS 表中记录的查询 −

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_ORDERS table −

SELECT * FROM CUSTOMER_ORDERS;

显示的表如下 −

The table displayed is as follows −

NAME

customer_id

Ramesh

NULL

Khilan

2

Kaushik

3

Kaushik

3

Chailtali

4

Hardik

NULL

Komal

NULL

Muffy

NULL

Copying Specific Records

我们还可以将 SQL SELECT INTO 语句与 WHERE 子句一起使用，以创建一个新表并从中复制特定行到现有表。

We can also use the SQL SELECT INTO statement with a WHERE clause to create a new table and copy specific rows from an existing table into it.

Syntax

以下是将 SELECT INTO 语句与 WHERE 子句一起使用的语法 −

Following is the syntax for using SELECT INTO statement with a WHERE clause −

SELECT *
INTO new_table_name
FROM existing_table_name
WHERE condition;

Example

使用以下查询，我们正在创建一个名为 NameStartsWith_K 的新表，其中包含 CUSTOMERS 表中的所有列，但它仅存储名称以 "k" 开头的客户的记录。

Using the following query we are creating a new table called NameStartsWith_K that includes all columns from the CUSTOMERS table, but it only stores the records of the customers whose name starts with "k".

SELECT *
INTO NameStartsWith_K
FROM CUSTOMERS
WHERE NAME LIKE 'k%';

Output

我们得到以下结果。我们可以观察到有 3 行已修改。

We get the following result. We can observe that 3 rows have been modified.

(3 rows affected)

Verification

我们可以通过使用 SELECT 语句检索其内容来验证更改是否反映在表中。

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement.

SELECT * from NameStartsWith_K;

显示的表如下 −

The table displayed is as follows −

ID

NAME

AGE

ADDRESS

SALARY

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

6

Komal

22

Hyderabad

4500.00