insert sql语句_SQL Insert语句概述「建议收藏」

insert sql语句

This article on the SQL Insert statement, is part of a series on string manipulation functions, operators and techniques. The previous articles are focused on SQL query techniques, all centered around the task of data preparation and data transformation.

有关SQL Insert语句的本文，是有关字符串操作函数，运算符和技术的系列文章的一部分。 先前的文章集中于SQL查询技术，所有这些技术都围绕数据准备和数据转换的任务。

So far we’ve been focused on select statement to read information out of a table. But that begs the question; how did the data get there in the first place? In this article, we’ll focus on the DML statement, the SQL insert statement. If we want to create a data, we’re going to use the SQL keyword, “Insert”.

到目前为止，我们一直专注于select语句以从表中读取信息。 但这引出了问题； 数据是如何到达那里的？ 在本文中，我们将重点介绍DML语句，即SQL插入语句。 如果要创建数据，将使用SQL关键字“ Insert”。

The general format is the INSERT INTO SQL statement followed by a table name, then the list of columns, and then the values that you want to use the SQL insert statement to add data into those columns. Inserting is usually a straightforward task. It begins with the simple statement of inserting a single row. Many times, however, it is more efficient to use a set-based approach to create new rows. In the latter part of the article, let’s discuss various techniques for inserting many rows at a time.

通用格式是INSERT INTO SQL语句，后跟一个表名，然后是列列表，然后是您要使用SQL insert语句将数据添加到这些列中的值。 插入通常是一项简单的任务。 它从插入单行的简单语句开始。 但是，很多时候，使用基于集合的方法来创建新行更为有效。 在本文的后半部分，让我们讨论一次插入许多行的各种技术。

前提条件 (Pre-requisite)

The assumption is that you’ve the following the permission to perform the insert operation on a table

假设您具有以下对表执行插入操作的权限

• sysadmin fixed server role, the sysadmin固定服务器角色， db_owner and db_owner和db_datawriter fixed database roles, and the table owner. db_datawriter固定数据库角色以及表所有者的成员。
• OPENROWSET BULK option requires a user to be a member of the OPENROWSET BULK选项插入时，要求用户是sysadmin fixed server role or of the sysadmin固定服务器角色或bulkadmin fixed server role. bulkadmin固定服务器角色的成员。
• here 在此处下载AdventureWorks2014

规则： (Rules: )

• Typically we don’t always provide data for every single column. In some cases, the columns can be left blank and in some other provide their own default values.

  通常，我们并不总是为每一列提供数据。 在某些情况下，这些列可以保留为空白，而在另一些情况下，可以提供其自己的默认值。

• You also have situations where some columns are automatically generating keys. In such cases, you certainly don’t want to try and insert your own values in those situations.

  您还会遇到某些列会自动生成键的情况。 在这种情况下，您当然不想在这种情况下尝试插入自己的值。

• The columns and values must match order, data type and number

  列和值必须匹配顺序，数据类型和编号

• If the column is of strings or date time or characters, they need to be enclosed in the in the single quotes. If they’re numeric, you don’t need the quotes.

  如果该列是字符串，日期时间或字符，则需要将它们括在单引号中。 如果它们是数字，则不需要引号。

• If you do not list your target columns in the insert statement then you must insert values into all of the columns in the table, also, be sure to maintain the order of the values

  如果未在insert语句中列出目标列，则必须将值插入表中的所有列中，并且还请确保保持值的顺序

如何执行简单的插入 (How to perform a simple Insert)

Let’s start inserting the data into this simple department table. First, use the name of the table and then inside parenthesis, the name of the columns and then type in the values. So, name the columns that we are going to type in the values.

让我们开始将数据插入此简单的Department表中。 首先，使用表的名称，然后在括号内使用列的名称，然后键入值。 因此，命名我们要在值中键入的列。

CREATE TABLE department
(dno   INT
 PRIMARY KEY, 
 dname VARCHAR(20) NOT NULL, 
 loc   VARCHAR(50) NOT NULL
);

The following SQL Insert into statement inserts a row into the department. The columns dno, dname, and loc are listed and values for those columns are supplied. The order is also maintained in the same way as the columns in the table

以下SQL插入语句将一行插入部门。 列出了列dno，dname和loc，并提供了这些列的值。 订单的维护方式也与表中的列相同

INSERT INTO department
(dno, 
 dname, 
 loc
)
VALUES
(10, 
 'ENGINEERING', 
 'New York'
);

如何使用SSMS执行简单的插入 (How to perform a simple Insert using SSMS)

Inserting data into a table can be accomplished either using SQL Server Management Studio (SSMS), a GUI, or through Data Manipulation Language in the SQL Editor. Using GUI in SSMS is a quick and easy way to enter records directly to the table.

可以使用SQL Server Management Studio（SSMS），GUI或通过SQL编辑器中的数据操作语言来将数据插入表中。 在SSMS中使用GUI是将记录直接输入到表中的快速简便的方法。

Let’s go ahead and browse department table and right-click and go to edit top 200 rows.

让我们继续浏览部门表，右键单击并编辑前200行。

This will bring up an editor window where we can interact directly with the data. To type in the new values, come down to the bottom and start typing the values.

这将打开一个编辑器窗口，我们可以在其中直接与数据进行交互。 要输入新值，请滑到底部并开始输入值。

It is very useful in some case to familiarize yourself with what data that you’re about to enter into the table.

在某些情况下，使自己熟悉将要输入到表中的数据非常有用。

SELECT *
FROM department;

如何使用Insert into语句添加多行数据 (How to use an Insert into statement to add multiple rows of data )

In the following SQL insert into statement, three rows got inserted into the department. The values for all columns are supplied and are listed in the same order as the columns in the table. Also, multiple values are listed and separated by comma delimiter.

在以下SQL插入语句中，将三行插入到部门中。 提供了所有列的值，并以与表中列相同的顺序列出。 此外，列出了多个值，并用逗号分隔符分隔。

INSERT INTO department
(dno, 
 dname, 
 loc
)
VALUES
(40, 
 'Sales', 
 'NJ'
),
(50, 
 'Marketting', 
 'MO'
),
(60, 
 'Testing', 
 'MN'
);

如何使用Insert into语句添加具有默认值的数据 (How to use an Insert into statement to add data with default values)

Let us create a simple table for the demonstration. A table is created with integer column defined with default value 0 and another DateTime column defined with the default date timestamp value.

让我们为演示创建一个简单的表。 将创建一个表，该表的整数列定义为默认值0，另一个DateTime列定义为默认日期时间戳值。

CREATE TABLE demo
(id      INT DEFAULT 0, 
 hirdate DATETIME DEFAULT GETDATE()
);

Now, let us insert default value into the table Demo using a SQL insert into statement

现在，让我们使用SQL插入语句将默认值插入表Demo中

INSERT INTO demo
DEFAULT VALUES;
 
SELECT *
FROM demo;

Note: If all the columns of the table defined with default values then specify the DEFAULT VALUES clause to create a new row with all default values

注意：如果表的所有列均使用默认值定义，则指定DEFAULT VALUES子句以创建具有所有默认值的新行

Next, override the default values of the table with a SQL Insert into statement.

接下来，使用SQL Insert into语句覆盖表的默认值。

INSERT INTO demo
VALUES(1,'2018-09-28 08:49:00')
 
SELECT *
FROM demo;

Let us consider another example where the table is a combination of both default and non-default columns.

让我们考虑另一个示例，其中表是默认列和非默认列的组合。

DROP TABLE IF EXISTS Demo;
CREATE TABLE demo
(id      INT
 PRIMARY KEY IDENTITY(1, 1), 
 Name    VARCHAR(20), 
 hirdate DATETIME DEFAULT GETDATE()
);

In order to insert default values to the columns, you just need exclude the default columns from the insert list with a SQL insert into statement.

为了将默认值插入列，您只需要使用SQL insert into语句从插入列表中排除默认列即可。

INSERT INTO demo (name)
VALUES ('Prashanth'), ('Brian'), ('Ahmad');
 
SELECT *
FROM demo;

The following example you can see that the keyword DEFAULT is used to feed a value to the table in the values clause with a SQL Insert into statement

在以下示例中，您可以看到关键字DEFAULT用于通过SQL Insert into语句将值提供给values子句中的表

INSERT INTO demo(name,hirdate)
VALUES('Kiki',DEFAULT), ('Yanna',DEFAULT), ('Maya',DEFAULT);

如何使用插入将数据添加到标识列表 (How to use an Insert to add data to an identity column table)

The following example shows how to insert data to an identity column. In the sample, we are overriding the default behavior (IDENTITY property of the column) of the INSERT with the SET IDENTITY_INSERT statement and insert an explicit value into the identity column. In this case, three rows are inserted with the values 100, 101 and 102

以下示例显示了如何将数据插入到身份列。 在该示例中，我们将使用SET IDENTITY_INSERT语句覆盖INSERT的默认行为（该列的IDENTITY属性），并将一个明确的值插入到identity列中。 在这种情况下，将插入三行，其值分别为100、101和102

SET IDENTITY_INSERT Demo ON;
INSERT INTO demo
(id, 
 name, 
 hirdate
)
VALUES
(100, 
 'Bojan', 
 DEFAULT
),
(101, 
 'Milan', 
 DEFAULT
),
(102, 
 'Esat', 
 DEFAULT
);
SET IDENTITY_INSERT Demo OFF;
 
SELECT *
FROM demo;

如何使用SQL插入语句从另一个数据集中添加数据 (How to use a SQL insert statement to add data from another dataset )

In this section, we’ll see how to capture the results of a query (simple select or multi table complex select) into another table.

在本节中，我们将看到如何将查询结果（简单选择或多表复杂选择）捕获到另一个表中。

The following example shows how to insert data from one table into another table by using INSERT…SELECT or INSERT…EXECUTE or SELECT * INTO . Each is based on a multi-table SELECT statement that includes an expression and a literal value in the column list.

下面的示例演示如何使用INSERT…SELECT或INSERT…EXECUTE或SELECT * INTO将数据从一个表插入到另一个表。 每个都基于多表SELECT语句，该语句在列列表中包含一个表达式和一个文字值。

INSERT…SELECT语句 (INSERT…SELECT statement)

The first SQL INSERT statement uses an INSERT…SELECT statement to derive the output from the multiple source tables such as Employee, EmployeePayHistory, Department, and Person of the AdventureWorks2014 database and insert the result set into the demo table.

第一个SQL INSERT语句使用INSERT…SELECT语句从多个源表（例如AdventureWorks2014数据库的Employee，EmployeePayHistory，Department和Person）中派生输出，并将结果集插入到演示表中。

You can see that schema and definition is already built for the INSERT INTO SQL statement.

您可以看到已经为INSERT INTO SQL语句构建了架构和定义。

CREATE TABLE Demo
(FirstName VARCHAR(25), 
 LastName  VARCHAR(25), 
 JobTitle  VARCHAR(100), 
 Rate      DECIMAL(7, 4), 
 GroupName VARCHAR(50)
);
 
INSERT INTO Demo
       SELECT P.FirstName, 
              P.LastName, 
              EMP.JobTitle, 
              EPH.Rate, 
              Dept.GroupName
       FROM HumanResources.EmployeePayHistory EPH
            INNER JOIN HumanResources.Employee EMP ON EMP.BusinessEntityID = EPH.BusinessEntityID
            INNER JOIN HumanResources.EmployeeDepartmentHistory H ON EMP.BusinessEntityID = H.BusinessEntityID
            INNER JOIN HumanResources.Department Dept ON H.DepartmentID = Dept.DepartmentID
            INNER JOIN Person.Person P ON P.BusinessEntityID = EMP.BusinessEntityID
       WHERE Dept.GroupName = 'Research and Development';
 
 
SELECT *
FROM Demo;

INSERT…EXECUTE语句 (INSERT…EXECUTE statement)

The second INSERT… EXECUTE statement, the stored procedure is executed and that contains the SELECT statement. The following example, the tb_spaceused table is created.

第二条INSERT…EXECUTE语句，将执行存储过程，其中包含SELECT语句。 下面的示例创建了tb_spaceused表。

CREATE TABLE tb_spaceused
(database_name       NVARCHAR(128), 
 database_size       VARCHAR(18), 
 [unallocated space] VARCHAR(18), 
 reserved            VARCHAR(18), 
 data                VARCHAR(18), 
 index_size          VARCHAR(18), 
 unused              VARCHAR(18)
);

The INSERT INTO SQL statement uses the EXECUTE clause to invoke a stored procedure that contains the result set of the SELECT statement.

INSERT INTO SQL语句使用EXECUTE子句来调用包含SELECT语句的结果集的存储过程。

INSERT INTO tb_spaceused
EXEC sp_msforeachdb 
     @command1 = "use ? exec sp_spaceused  @oneresultset = 1";
 
SELECT *
FROM tb_spaceused;

SELECT * INTO语句 (SELECT * INTO statement)

The third, in this case, you want to create a new table having the same set of columns as an existing table or simple select or complex select statement.

第三种，在这种情况下，您要创建一个新表，该表具有与现有表或简单选择或复杂选择语句相同的列集。

仅复制架构 (Copy schema only )

For example, you may want to create just the structure of the demo table and call it demo_ duplicate and you don’t want to the copy the rows. In this case, use FALSE condition in the WHERE clause (1 <>2 or 1=0).

例如，您可能只想创建演示表的结构并将其命名为demo_plicate，而又不想复制行。 在这种情况下，请在WHERE子句中使用FALSE条件（1 <> 2或1 = 0）。

SELECT *
INTO DEMO_Duplicate
FROM Demo
WHERE 1 <> 2;

SELECT *
FROM DEMO_Duplicate

sp_help 'DEMO_Duplicate'

Note: In this case, the demo table is already created in the first method. I’m using the same table for this demonstration.

注意：在这种情况下，演示表已在第一种方法中创建。 我正在使用同一张表进行演示。

复制架构和数据 (Copy schema and data )

The following example copies both schema and data to the target table.

下面的示例将模式和数据都复制到目标表。

SELECT *
INTO DEMO_Duplicate
FROM Demo

摘要 (Summary)

Thus far, we discussed standards, rules, guidelines for the SQL Insert statement. You could insert any values if it’s coupled with select statement and matches with the target schema. Thanks for reading this article and if you have any questions, feel free to ask in the comments below.

到目前为止，我们讨论了SQL Insert语句的标准，规则和准则。 如果将其与select语句耦合并与目标模式匹配，则可以插入任何值。 感谢您阅读本文，如果有任何疑问，请随时在下面的评论中提问。

翻译自: https://www.sqlshack.com/overview-of-the-sql-insert-statement/

insert sql语句