在SQL Server中插入IN-T-SQL语句

最新推荐文章于 2024-03-18 16:34:09 发布

culuo4781

最新推荐文章于 2024-03-18 16:34:09 发布

阅读量1.8k

点赞数

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

原文链接：https://www.sqlshack.com/insert-into-t-sql-statement-in-sql-server/

版权

In this article, we will go deeply through the INSERT INTO statement by showing the different syntax formats and usage scenarios for that statement.

在本文中，我们将通过展示INSERT INTO语句的不同语法格式和使用场景，来深入了解该语句。

The T-SQL language is a rich database programming language that provides us with a large number of dynamic statements and functions that help us to perform any functionality in different ways, based on our development skills.

T-SQL语言是一种丰富的数据库编程语言，可根据我们的开发技能为我们提供大量动态语句和函数，这些语句和函数可帮助我们以不同的方式执行任何功能。

用法 (Usage )

The INSERT INTO T-SQL statement is used mainly to add one or more rows to the target table or view in SQL Server. This can be done by providing constant values in the INSERT INTO statement or provide the source table or view from which we will copy the rows.

INSERT INTO T-SQL语句主要用于向SQL Server中的目标表或视图添加一个或多个行。这可以通过在INSERT INTO语句中提供常量值或提供源表或视图（从中复制行）来完成。

句法 (Syntax )

The INSERT INTO T-SQL statement has a dynamic syntax that fits all types of data insertion processes. For a detailed view of the INSERT INTO T-SQL statement, check the Microsoft INSERT INTO documentation.

INSERT INTO T-SQL语句具有适合所有类型的数据插入过程的动态语法。有关INSERT INTO T-SQL语句的详细视图，请查看Microsoft INSERT INTO文档。

The INSERT INTO T-SQL statement syntax that is used to insert a single row into a SQL Server database table or view is like:

用于将单行插入SQL Server数据库表或视图中的INSERT INTO T-SQL语句语法类似于：

INSERT INTO table (column1, column2, … )

INSERT INTO表（第1、2列，…）

VALUES (expression1, expression2, …);

值（表达式1，表达式2，...）；

And the INSERT INTO statement syntax that is used to insert multiple rows from a source database table is like:

用来从源数据库表插入多行的INSERT INTO语句语法类似于：

INSERT INTO table (column1, column2, … )

INSERT INTO表（第1、2列，…）

SELECT expression1, expression2, …

SELECT expression1，expression2，...

FROM source_tables

从source_tables

[WHERE conditions];

[条件]；

From the previous syntax statements:

根据之前的语法语句：

table is the name of the target table that the rows will be inserted into 表是将插入行的目标表的名称
Column1, column2 are the names of the columns in the target table that will be filled with the provided values Column1，column2是目标表中将使用提供的值填充的列的名称
expression1, expression2 are the values that will be assigned to the mentioned columns in the target table based on the provided order. Take into consideration that you should provide the values for all NOT NULL columns in the target table, and optionally provide values for NULL columns expression1，expression2是将根据提供的顺序分配给目标表中提到的列的值。考虑到您应该为目标表中的所有NOT NULL列提供值，并有选择地为NULL列提供值
source_tables is the name of the table from which the values will be copied and inserted into the target table. These values will be filtered based on the provided WHERE conditions source_tables是将从中复制值并将其插入到目标表中的表的名称。这些值将根据提供的WHERE条件进行过滤
INTO keyword of the INSERT INTO statement is optional INTO关键字是可选的

入门 (Getting started )

For this demo purposes, we will create a new testing table, that contains an IDENTITY column, NULL, and NOT NULL columns, and we will perform the changes gradually on that table to cover most of the common use cases for the INSERT INTO T-SQL statement.

为此，我们将创建一个新的测试表，其中包含一个IDENTITY列，NULL和NOT NULL列，并且我们将对该表进行逐步更改，以涵盖INSERT INTO T-的大多数常见用例。 SQL语句。

The demo table can be created using the CREATE TABLE T-SQL statement below:

可以使用下面的CREATE TABLE T-SQL语句创建演示表：

CREATE TABLE InsertDemo
(
  ID INT IDENTITY PRIMARY KEY,
  StName NVARCHAR (100) NOT NULL,
  StBirthDate DateTime NOT NULL,
  StGrade int NULL,
  StAddress NVARCHAR (MAX) NULL,
  StParentsPhone VARCHAR(25) NOT NULL
 )

例子 (Examples )

The simple way to use the INSERT INTO statement to add a new record to the created table is providing the values in constant format, where the values will be provided for all NULL and NOT NULL columns, except for the auto-generated columns, in the correct order for the columns in the target table, as in the T-SQL statement below:

使用INSERT INTO语句向创建的表添加新记录的简单方法是以恒定格式提供值，其中将为自动生成的列中的所有NULL和NOT NULL列（自动生成的列除外）提供值。目标表中列的正确顺序，如下面的T-SQL语句所示：

INSERT INTO InsertDemo 
 VALUES ('John','2000-08-12',5,'London','56896652')

Checking the inserted row in the target table, you will see that the record is inserted successfully and assigned an auto-generated ID value of 1, as shown below:

检查目标表中插入的行，您将看到记录已成功插入并分配了自动生成的ID值1，如下所示：

To insert multiple records in the same INSERT INTO statement, instead of writing multiple insert statements, we can provide the values for each row in comma-separated format, as in the T-SQL statement below that inserts a new three rows to the demo table:

要在同一个INSERT INTO语句中插入多个记录，而不是编写多个insert语句，我们可以以逗号分隔的格式提供每一行的值，如下面的T-SQL语句那样，在演示表中插入新的三行：

INSERT INTO InsertDemo 
 VALUES ('Jack','2000-02-10',5,'London','56552244')
       ,('Daniel','2000-07-24',5,'Oxford','56448899')
     ,('Gonzalo','2000-01-13',5,'Cambridge','56254896')

Checking the table for the newly inserted rows, you will see that a new three records are inserted into the target table, and assigned auto-incremented ID values, as shown below:

检查表中是否有新插入的行，您将看到新的三个记录被插入到目标表中，并分配了自动递增的ID值，如下所示：

In order to insert values for specific columns only, we should mention the name of these columns and provide the values for these columns in the same order as in the columns list, taking into consideration that all NOT NULL columns are listed and assigned values, as in the T-SQL statement below:

为了只为特定列插入值，我们应该提及这些列的名称，并以与列列表相同的顺序提供这些列的值，同时考虑到所有NOT NULL列均已列出并分配了值，例如在下面的T-SQL语句中：

INSERT INTO InsertDemo (StName,StBirthDate,StParentsPhone )
 VALUES ('Zaid','2000-04-12','56986532')

In the target table, you will see that a new row is inserted with the assigned columns’ values and NULL value for the NULL columns that are not mentioned in the columns list of the INSERT INTO T-SQL statement, as shown below:

在目标表中，您将看到一个新行，其中插入了分配行的值和INSERT INTO T-SQL语句的列列表中未提及的NULL列的NULL值，如下所示：

You can also provide the list of columns in the INSERT INTO statement in a different order from the order of the columns in the target table, making sure that you assign values for the columns in the same order as listed in the INSERT INTO statement, as in the T-SQL statement below:

您还可以以与目标表中列顺序不同的顺序提供INSERT INTO语句中的列列表，并确保以与INSERT INTO语句中列出的顺序相同的顺序为列分配值，如下所示：在下面的T-SQL语句中：

INSERT INTO InsertDemo (StParentsPhone, StName ,StBirthDate)
 VALUES ('56458722', 'David','2000-12-06')

And the record will be inserted successfully, with the correct value assigned for each column, as shown below:

并将成功插入记录，并为每列分配正确的值，如下所示：

If you try to assign values to the name and birthdate columns only in the INSERT INTO T-SQL statement, as in the below statement:

如果您尝试仅在INSERT INTO T-SQL语句中为name和birthdate列分配值，如以下语句所示：

INSERT INTO InsertDemo (StName ,StBirthDate)
 VALUES ( 'Swathi','2000-11-04')

The statement execution will fail, as you should assign value for the phone number NOT NULL column in the INSERT INTO T-SQL statement, recalling that all NOT NULL columns are mandatory columns, as shown in the error message below:

语句执行将失败，因为您应该为INSERT INTO T-SQL语句中的电话号码NOT NULL列分配值，并回想所有的NOT NULL列都是必需列，如以下错误消息所示：

Let us modify the target table by adding a new computed column that is used to calculate the age of each student, as shown below:

让我们通过添加一个新的计算列来修改目标表，该列用于计算每个学生的年龄，如下所示：

ALTER TABLE InsertDemo ADD AGE AS DATEDIFF(YEAR, StBirthDate, GETDATE())
GO

Remember that, the computed column value will be calculated automatically based on the defined equation, without being able to insert that value explicitly.

请记住，所计算的列值将基于定义的方程式自动计算，而无需显式插入该值。

Checking the target table’s data again, you will see that the age is calculated automatically for all students, as shown below:

再次检查目标表的数据，您会看到为所有学生自动计算了年龄，如下所示：

If you try to insert explicit value for the computed column, using the INSERT INTO statement below:

如果尝试为计算列插入显式值，请使用下面的INSERT INTO语句：

INSERT INTO InsertDemo (StName ,StBirthDate,StParentsPhone,AGE)
 VALUES ( 'Prashanth','2000-03-15','594214478',12)

The statement execution will fail, showing that you cannot modify the automatically calculated value of the computed column, as shown below:

语句执行将失败，表明您无法修改计算列的自动计算值，如下所示：

Also, if you try to insert explicit value for the ID column, with IDENTITY property, that is automatically incremented and generated, as in the INSERT INTO statement below:

另外，如果您尝试为ID列插入具有IDENTITY属性的显式值，该值会自动递增并生成，如下面的INSERT INTO语句所示：

INSERT INTO InsertDemo (ID,StName ,StBirthDate,StParentsPhone)
 VALUES (12, 'Prashanth','2000-03-15','594214478')

The INSERT statement execution will fail, showing that you cannot insert explicit value for the identity column, that is automatically generated, as in the error message below:

INSERT语句执行将失败，表明您无法为自动生成的标识列插入显式值，如以下错误消息所示：

In order to allow inserting an explicit value for the identity column, we need to enable the IDENTITY_INSERT property before executing the INSERT INTO statement and disable it after inserting the value, making sure that you mention the name of all NOT NULL columns and the identity column, and assign values for the for all these columns in the correct order, as in the T-SQL statement below:

为了允许为identity列插入一个显式值，我们需要在执行INSERT INTO语句之前启用IDENTITY_INSERT属性，并在插入该值之后禁用它，并确保提及所有NOT NULL列和identity列的名称，并按照正确的顺序为所有这些列分配值，如下面的T-SQL语句所示：

 SET IDENTITY_INSERT InsertDemo ON
 INSERT INTO InsertDemo (ID,StName ,StBirthDate,StParentsPhone)
 VALUES (12, 'Prashanth','2000-03-15','594214478') 
 SET IDENTITY_INSERT InsertDemo OFF

And the new row will be inserted successfully with the explicitly provided identity value. Providing an explicit value for the identity column is not highly recommended unless you plan to copy a record to another table with the same identity value, as it will make a gap in the identity values and will start counting after the provided ID value, as shown below:

并且新行将使用显式提供的标识值成功插入。除非您打算将一条记录复制到具有相同标识值的另一个表中，否则不建议为Identity列提供一个明确的值，因为这将使标识值产生差异，并在提供的ID值之后开始计数，如下所示下面：

Let us modify the target table again, by adding a new uniqueidentifier NULL column, taking into consideration that we should provide a default value to be assigned to the existing columns in case we plan to add the GUID column as NOT NULL. The new column can be added using the ALTER TABLE T-SQL statement below:

考虑到在我们计划将GUID列添加为NOT NULL的情况下，考虑到我们应该提供一个默认值以分配给现有列，让我们再次修改目标表，方法是添加一个新的uniqueidentifier NULL列。可以使用下面的ALTER TABLE T-SQL语句添加新列：

ALTER TABLE InsertDemo ADD STD_Guid uniqueidentifier

The uniqueidentifier column can be assigned values using the NEWID() built-in function that generates a unique value at each call, which can be easily used in the INSERT INTO statement, as below:

可以使用NEWID（）内置函数为uniqueidentifier列分配值，该函数在每次调用时都会生成一个唯一值，可以在INSERT INTO语句中轻松使用它，如下所示：

INSERT INTO InsertDemo (STD_Guid, StName ,StBirthDate,StParentsPhone)
 VALUES (NEWID(), 'Johnson','2000-05-12','59785425')

Checking the target table, you will see that a NULL value is assigned to that GUID column for all previously existing columns, and a unique GUID value assigned to the newly inserted column, using the NEWID() function, as shown below:

检查目标表，您将看到使用NEWID（）函数为所有先前存在的列分配了一个NULL值，并给GUID列分配了唯一的GUID值，如下所示：

The INSERT INTO statement can be used to add values to the target table from another source table, in which you need to provide the list of columns in the target table and the related columns from the source table that have the values to be assigned to the target table columns, as in the T-SQL statement below:

INSERT INTO语句可用于将值从另一个源表添加到目标表，在该源表中，您需要提供目标表中的列列表以及源表中具有要分配给值的相关列。目标表列，如下面的T-SQL语句所示：

INSERT INTO [dbo].[InsertDemo] (STD_Guid, StName ,StBirthDate,StParentsPhone)
SELECT TOP (10) P.[rowguid], P.[FirstName] + P.[MiddleName] + P.[LastName]  AS StName, P.[ModifiedDate], PP.PhoneNumber
FROM [Person].[Person] P
JOIN [Person].[PersonPhone] PP
ON P.BusinessEntityID=PP.BusinessEntityID
WHERE P.[FirstName] + P.[MiddleName] + P.[LastName] IS NOT NULL and PP.PhoneNumber IS NOT NULL

The previous INSERT INTO statement is used to copy 10 records from two source tables based on the provided logic, and the inserted records will be as below:

前面的INSERT INTO语句用于根据提供的逻辑从两个源表中复制10条记录，插入的记录如下：

We can modify the previous INSERT INTO statement by controlling the number of inserted columns using the TOP option in the INSERT clause rather than controlling it in the SELECT statement from the source table, as shown below:

我们可以通过使用INSERT子句中的TOP选项控制插入的列数，而不是通过源表的SELECT语句来控制插入的列数，从而修改先前的INSERT INTO语句，如下所示：

INSERT TOP (10) INTO [dbo].[InsertDemo] (STD_Guid, StName ,StBirthDate,StParentsPhone)
SELECT P.[rowguid], P.[FirstName] + P.[MiddleName] + P.[LastName]  AS StName, P.[ModifiedDate], PP.PhoneNumber
FROM [Person].[Person] P
JOIN [Person].[PersonPhone] PP
ON P.BusinessEntityID=PP.BusinessEntityID
WHERE P.[FirstName] + P.[MiddleName] + P.[LastName] IS NOT NULL and PP.PhoneNumber IS NOT NULL

And the 10 records will be copied from the source table and inserted to the inserted table, as shown in the result below:

10条记录将从源表中复制并插入到插入的表中，如以下结果所示：

INSERT INTO statement can also be used to add rows to a database table located in a remote server using the OPENQUERY statement when there is a linked server to connect to that remote SQL Server or using OPENDATASOURCE statement using a specific connection string, as in the T-SQL statement below:

当有链接服务器连接到该远程SQL Server时，或者使用OPENDATASOURCE语句，并且使用特定的连接字符串，如T中所示，也可以使用INSERT INTO语句向使用OPENQUERY语句的远程服务器中的数据库表中添加行。 -SQL语句如下：

INSERT INTO OPENDATASOURCE('SQLNCLI',  
    'Data Source= .; Integrated Security=SSPI')  
    .AdventureWorks2016.dbo.InsertDemo (STD_Guid, StName ,StBirthDate,StParentsPhone) 
    VALUES (NEWID(), 'Fidal','2000-09-11','59785777') ;

And the new record will be inserted to that remote database table, as shown below:

并将新记录插入到该远程数据库表中，如下所示：

It is beneficial to mention that the OUTPUT clause can be used with the INSERT INTO statement to retrieve information from about each row added by the executed INSERT statement. This information can be used later as a confirmation message from the application or for data archiving purposes.

值得一提的是， OUTPUT子句可以与INSERT INTO语句一起使用，以检索有关已执行的INSERT语句添加的每一行的信息。此信息以后可以用作来自应用程序的确认消息或用于数据归档。

In the example below, we create a temp table to store the inserted students’ names, as in this T-SQL statement:

在下面的示例中，我们创建一个临时表来存储插入的学生的姓名，如以下T-SQL语句所示：

 CREATE TABLE #InsertAudit (StName NVARCHAR (100))
 
 INSERT INTO InsertDemo (STD_Guid, StName ,StBirthDate,StParentsPhone)
 OUTPUT INSERTED.StName INTO #InsertAudit  
 VALUES (NEWID(), 'Doni','2000-07-10','59785887') 
 GO
 SELECT * FROM #InsertAudit

Querying the temp table, the inserted student name will be displayed, as below:

查询临时表，将显示插入的学生姓名，如下所示：