INSERT INTO SELECT语句概述和示例

最新推荐文章于 2024-06-04 18:10:57 发布

culuo4781

最新推荐文章于 2024-06-04 18:10:57 发布

阅读量9.1k

点赞数

文章标签：数据库 mysql java sql python

原文链接：https://www.sqlshack.com/sql-insert-into-select-statement-overview-and-examples/

版权

This article covers the SQL INSERT INTO SELECT statement along with its syntax, examples and use cases.

本文介绍了SQL INSERT INTO SELECT语句及其语法，示例和用例。

In my earlier article SQL SELECT INTO Statement, we explored the following tasks.

在我之前的文章SQL SELECT INTO Statement中，我们探索了以下任务。

Create a SQL table on the fly while inserting records with appropriate data types
在插入具有适当数据类型的记录的同时动态创建SQL表
Use SQL SELECT INTO to insert records in a particular FileGroup
使用SQL SELECT INTO将记录插入特定的FileGroup中
We cannot use it to insert data in an existing table
我们不能使用它在现有表中插入数据

INSERT INTO SELECT语句 (The INSERT INTO SELECT statement)

We want to insert records as a regular database activity. We can insert data directly using client tools such as SSMS, Azure Data Studio or directly from an application. In SQL, we use the SQL INSERT INTO statement to insert records.

我们希望将记录作为常规数据库活动插入。我们可以使用客户端工具（例如SSMS，Azure Data Studio）直接插入数据，也可以直接从应用程序插入数据。在SQL中，我们使用SQL INSERT INTO语句插入记录。

INSERT INTO的语法 (The syntax of the INSERT INTO)

Once we insert data into the table, we can use the following syntax for our SQL INSERT INTO statement.

将数据插入表后，可以对SQL INSERT INTO语句使用以下语法。

INSERT INTO table_name (Column1, Column 2....)
VALUES (value1, value2, ...);

If we have specified all column values as per table column orders, we do not need to specify column names. We can directly insert records into the table.

如果我们已按照表的列顺序指定了所有列值，则无需指定列名。我们可以直接将记录插入表中。

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

Let us create a sample table and insert data into it.

让我们创建一个示例表并将数据插入其中。

CREATE TABLE Employees
(ID   INT, 
 Name VARCHAR(20)
);

We can insert data using the following queries. Both queries are valid for data insertion.

我们可以使用以下查询插入数据。这两个查询对于数据插入均有效。

Insert into Employees (ID, Name) values (1,'raj')
Insert into Employees values (2,'raj')

We cannot insert data without specifying column names if there is a mismatch between data insertion and the order of column values is different. We can get the following error message.

如果数据插入之间不匹配并且列值的顺序不同，则无法在不指定列名的情况下插入数据。我们会收到以下错误消息。

Column name or number of supplied values does not match table definition.
列名或提供的值数与表定义不匹配。
Conversion failed when converting the varchar value ‘raj’ to data type int.

将varchar值“ raj”转换为数据类型int时，转换失败。

In this example, we’ll use the SQL INSERT INTO statement with supplying values directly in a statement. Suppose we want to insert data from another table. We can still use the SQL INSERT INTO statement with a select statement. Let’s explore this in the next section.

在此示例中，我们将使用SQL INSERT INTO语句，直接在语句中提供值。假设我们要从另一个表插入数据。我们仍然可以将SQL INSERT INTO语句与select语句一起使用。让我们在下一部分中对此进行探讨。

INSERT INTO SELECT语句语法 (INSERT INTO SELECT Statement Syntax)

We can insert data from other SQL tables into a table with the following INSERT INTO SELECT statement.

我们可以使用以下INSERT INTO SELECT语句将其他SQL表中的数据插入表中。

INSERT INTO table1 (col1, col2, col3, …)
SELECT col1, col2, col3, …
FROM table2

This query performs the following tasks:

该查询执行以下任务：

It first Selects records from a table ( Select statement)
首先从表中选择记录（Select语句）
Next, it inserts into a table specified with INSERT INTO
接下来，它将插入到用INSERT INTO指定的表中

Note:注意： The Column structure should match between the column returned by SELECT statement and destination table.Column结构应在SELECT语句返回的列与目标表之间匹配。

INSERT INTO SELECT示例 (INSERT INTO SELECT examples)

示例1：将数据从源表的所有列插入目标表 (Example 1: insert data from all columns of source table to destination table)

We have the following records in an existing Employee table.

现有的Employee表中有以下记录。

Let us create another table Customers with the following query.

让我们用以下查询创建另一个表Customers。

CREATE TABLE Customers
(ID   INT, 
 Name VARCHAR(20)
);

We want to insert all records from the Employees table to the Customers table. We can use the SQL INSERT INTO SELECT statement to do this.

我们希望将所有记录从“雇员”表插入“客户”表。我们可以使用SQL INSERT INTO SELECT语句执行此操作。

INSERT INTO Customers
       SELECT *
       FROM Employees;

It inserts all records into the Customers table. We can verify the records in Customers table are similar to the Employees table.

它将所有记录插入到客户表。我们可以验证“客户”表中的记录是否类似于“员工”表。

In this example, we inserted records for all columns to the Customers table.

在此示例中，我们将所有列的记录插入到“客户”表中。

示例2：通过指定列名将行从源表插入到目标表 (Example 2: Insert rows from source to destination table by specifying column names)

Let’s drop the existing Customers table before we move forward. Now, we want to create a table with one additional IDENTITY column. IDENTITY column automatically inserts identity values in a table. We also added a City column that allows NULL values

在继续前进之前，让我们删除现有的Customers表。现在，我们要创建一个带有附加IDENTITY列的表。 IDENTITY列会自动在表中插入标识值。我们还添加了一个允许NULL值的City列

CREATE TABLE Customers
(ID     INT IDENTITY(1, 1), 
 Emp_ID INT, 
 Name   VARCHAR(20), 
 City   VARCHAR(20) NULL,
);

We cannot use the INSERT INTO SELECT statement similar to the above example. If we try to run this code, we get an error message.

我们不能使用类似于上面示例的INSERT INTO SELECT语句。如果我们尝试运行此代码，则会收到错误消息。

INSERT INTO Customers
       SELECT *
       FROM Employees;

In this case, we need to specify the column name with INSERT INTO statement.

在这种情况下，我们需要使用INSERT INTO语句指定列名称。

INSERT INTO Customers (Emp_ID ,Name)
       SELECT *
       FROM Employees;

In the Customers table, we have an additional column with allows NULL values. Let’s run a Select on Customers table. In the following screenshot, we can see NULL values in the City column.

在“ 客户”表中，我们还有一列允许NULL值。让我们运行“在客户上选择”表。在以下屏幕截图中，我们可以在“城市”列中看到NULL值。

Suppose you have a different column in the source table. You can still insert records into the destination table with specifying column names in the INSERT INTO SELECT statement. We should have an appropriate data type to insert data. You cannot insert a varchar column data into an INT column.

假设源表中有其他列。您仍可以在INSERT INTO SELECT语句中指定列名，将记录插入目标表中。我们应该有适当的数据类型来插入数据。您不能将varchar列数据插入INT列。

Add a new column in Employees table using ALTER TABLE statement.

使用ALTER TABLE语句在雇员表中添加新列。

ALTER TABLE Employees
ADD Country varchar(50);

Update the table records with country value India.

使用国家/地区值India更新表记录。

Update Employees set Country='India'

Now, rerun the INSERT INTO SELECT statement. You can notice that we are using SELECT * instead of specifying column names.

现在，重新运行INSERT INTO SELECT语句。您会注意到我们使用的是SELECT *而不是指定列名。

INSERT INTO Customers (Emp_ID ,Name)
       SELECT *
       FROM Employees;

We get the following error message. This error comes because of the column mismatch between the source table and destination table.

我们收到以下错误消息。发生此错误是由于源表和目标表之间的列不匹配。

SQL Server INSERT INTO SELECT Error messsage

We can map the column between the source and destination table using the following query.

我们可以使用以下查询在源表和目标表之间映射该列。

INSERT INTO Customers
(Emp_ID, 
 Name
)
       SELECT ID,Name 
       FROM Employees;

示例3：使用INSERT INTO SELECT语句插入顶部行 (Example 3: Insert top rows using the INSERT INTO SELECT statement)

Suppose we want to insert Top N rows from the source table to the destination table. We can use Top clause in the INSERT INTO SELECT statement. In the following query, it inserts the top 1 row from the Employees table to the Customers table.

假设我们要将源表的前N行插入目标表。我们可以在INSERT INTO SELECT语句中使用Top子句。在以下查询中，它将雇员表中的前1行插入到客户表中。

INSERT TOP(1) INTO Customers
(Emp_ID, 
 Name
)
       SELECT ID,Name 
       FROM Employees;

示例4：在SQL INSERT INTO SELECT语句中同时使用列和定义的值进行插入 (Example 4: Insert using both columns and defined values in the SQL INSERT INTO SELECT Statement)

In previous examples, we either specified specific values in the INSERT INTO statement or used INSERT INTO SELECT to get records from the source table and insert it into the destination table.

在前面的示例中，我们要么在INSERT INTO语句中指定了特定值，要么使用INSERT INTO SELECT从源表中获取记录并将其插入到目标表中。

We can combine both columns and defined values in the SQL INSERT INTO SELECT statement.

我们可以在SQL INSERT INTO SELECT语句中组合列和定义的值。

We have the following columns in the Customers and Employees table. Previously, we did not insert any values for the City column. We do not have the required values in the Employee table as well. We need to specify an explicit value for the City column.

在“客户和雇员”表中有以下几列。以前，我们没有为“城市”列插入任何值。我们在Employee表中也没有必需的值。我们需要为“城市”列指定一个明确的值。

In the following query, we specified a value for the City column while the rest of the values we inserted from the Employees table.

在以下查询中，我们为“城市”列指定了一个值，而其余的值是从“雇员”表中插入的。

INSERT TOP(1) INTO Customers (Emp_ID,  Name, City)
       SELECT ID, Name,'Delhi' FROM Employees;

In the following query, we can see it inserts one row (due to Top (1) clause) along with value for the City column.

在下面的查询中，我们可以看到它插入一行（由于Top（1）子句）以及City列的值。

示例5：带有Join子句的INSERT INTO SELECT语句从多个表中获取数据 (Example 5: INSERT INTO SELECT statement with Join clause to get data from multiple tables)

We can use a JOIN clause to get data from multiple tables. These tables are joined with conditions specified with the ON clause. Suppose we want to get data from multiple tables and insert into a table.

我们可以使用JOIN子句从多个表中获取数据。这些表与ON子句指定的条件结合在一起。假设我们要从多个表中获取数据并将其插入一个表中。

In this example, I am using AdventureWorks2017 database. First, create a new table with appropriate data types.

在此示例中，我正在使用AdventureWorks2017数据库。首先，使用适当的数据类型创建一个新表。

CREATE TABLE [HumanResources].[EmployeeData](
  [FirstName] [dbo].[Name] NOT NULL,
  [MiddleName] [dbo].[Name] NULL,
  [LastName] [dbo].[Name] NOT NULL,
  [Suffix] [nvarchar](10) NULL,
  [JobTitle] [nvarchar](50) NOT NULL,
  [PhoneNumber] [dbo].[Phone] NULL,
  [PhoneNumberType] [dbo].[Name] NULL,
  [EmailAddress] [nvarchar](50) NULL,
  [City] [nvarchar](30) NOT NULL,
  [StateProvinceName] [dbo].[Name] NOT NULL,
  [PostalCode] [nvarchar](15) NOT NULL,
  [CountryRegionName] [dbo].[Name] NOT NULL
) ON [PRIMARY]
GO

This table should contain records from the output of a multiple table join query. Execute the following query to insert data into HumanResources.EmployeeData table.

该表应包含多表联接查询的输出中的记录。执行以下查询以将数据插入HumanResources.EmployeeData表。

INSERT INTO HumanResources.EmployeeData 
SELECT p.[FirstName], 
       p.[MiddleName], 
       p.[LastName], 
       p.[Suffix], 
       e.[JobTitle], 
       pp.[PhoneNumber], 
       pnt.[Name] AS [PhoneNumberType], 
       ea.[EmailAddress], 
       a.[City], 
       sp.[Name] AS [StateProvinceName], 
       a.[PostalCode], 
       cr.[Name] AS [CountryRegionName]
FROM [HumanResources].[Employee] e
     INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
     INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID]
     INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
     INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
     INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
     LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.BusinessEntityID = p.[BusinessEntityID]
     LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
     LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] = ea.[BusinessEntityID];
GO

示例6：具有公共表表达式的INSERT INTO SELECT语句 (Example 6: INSERT INTO SELECT statement with common table expression)

We use Common Table Expressions (CTE) to simplify complex join from multiple columns. In the previous example, we used JOINS in a Select statement for inserting data into a SQL table. In this part, we will rewrite the query with CTE.

我们使用通用表表达式（CTE）来简化来自多个列的复杂联接。在前面的示例中，我们在Select语句中使用了JOINS将数据插入到SQL表中。在这一部分中，我们将使用CTE重写查询。

In a CTE, we can divide code into two parts.

在CTE中，我们可以将代码分为两部分。

We define CTE by a WITH clause before SELECT, INSERT, UPDATE, DELETE statement
我们通过SELECT，INSERT，UPDATE，DELETE语句之前的WITH子句定义CTE
Once we define CTE, we can take reference the CTE similar to a relational SQL table
定义CTE之后，就可以引用类似于关系SQL表的CTE

Execute the following code to insert data using a CTE.

执行以下代码以使用CTE插入数据。

WITH EmployeeData_Temp([FirstName], 
                       [MiddleName], 
                       [LastName], 
                       [Suffix], 
                       [JobTitle], 
                       [PhoneNumber], 
                       [PhoneNumberType], 
                       [EmailAddress], 
                       [City], 
                       [StateProvinceName], 
                       [PostalCode], 
                       [CountryRegionName])
     AS (
 
     SELECT p.[FirstName], 
            p.[MiddleName], 
            p.[LastName], 
            p.[Suffix], 
            e.[JobTitle], 
            pp.[PhoneNumber], 
            pnt.[Name] AS [PhoneNumberType], 
            ea.[EmailAddress], 
            a.[City], 
            sp.[Name] AS [StateProvinceName], 
            a.[PostalCode], 
            cr.[Name] AS [CountryRegionName]
     FROM [HumanResources].[Employee] e
          INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
          INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID]
          INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
          INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
          INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
          LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.BusinessEntityID = p.[BusinessEntityID]
          LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
          LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] = ea.[BusinessEntityID])
 
INSERT INTO HumanResources.EmployeeData
            SELECT *
            FROM EmployeeData_Temp;
GO

示例7：带有表变量的INSERT INTO SELECT语句 (Example 7: INSERT INTO SELECT statement with a Table variable)

We use Table variables similarly to a temporary table. We can declare them using the table data type. This table can be used to perform activities in SQL Server where we do not require a permanent table. You can divide the following query into three parts.

我们使用表变量类似于临时表。我们可以使用表数据类型声明它们。该表可用于在不需要永久表SQL Server中执行活动。您可以将以下查询分为三部分。

Create a SQL Table variable with appropriate column data types. We need to use data type TABLE for table variable
创建具有适当的列数据类型SQL表变量。我们需要为表变量使用数据类型TABLE
Execute a INSERT INTO SELECT statement to insert data into a table variable
执行INSERT INTO SELECT语句以将数据插入表变量
View the table variable result set
查看表变量结果集

DECLARE @TableVar table(  
    [JobTitle] [nvarchar](50) NOT NULL,
  [BirthDate] [date] NOT NULL,
  [MaritalStatus] [nchar](1) NOT NULL,
  [Gender] [nchar](1) NOT NULL,
  [HireDate] [date] NOT NULL,
  [SalariedFlag] [dbo].[Flag] NOT NULL,
  [VacationHours] [smallint] NOT NULL,
  [SickLeaveHours] [smallint] NOT NULL
  )
  
-- Insert values into the table variable.  
INSERT INTO @TableVar 
    SELECT  
   [JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[SalariedFlag]
      ,[VacationHours]
      ,[SickLeaveHours]
    FROM [AdventureWorks2017].[HumanResources].[Employee]
  
-- View the table variable result set.  
SELECT * FROM @TableVar;  
GO

结论 (Conclusion)

In this article, we explore the use cases of the INSERT INTO SELECT statement. I hope you found this article helpful. Feel free to provide feedback in the comments below.

在本文中，我们探讨了INSERT INTO SELECT语句的用例。希望本文对您有所帮助。请随时在下面的评论中提供反馈。

翻译自: https://www.sqlshack.com/sql-insert-into-select-statement-overview-and-examples/

culuo4781

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
INSERT INTO SELECT语句概述和示例

This article covers the SQL INSERT INTO SELECT statement along with its syntax, examples and use cases. 本文介绍了SQL INSERT INTO SELECT语句及其语法，示例和用例。 In my earlier article SQL SELECT INTO State...
复制链接

扫一扫