将数据插入SQL Server的方法

摘要 (Summary)

There are a variety of ways of managing data to insert into SQL Server. How we generate and insert data into tables can have a profound impact on performance and maintainability! This topic is often overlooked as a beginner’s consideration, but mistakes in how we grow objects can create massive headaches for future developers and administrators.

有多种方法可以管理要插入SQL Server的数据。 我们如何将数据生成和插入表中会对性能和可维护性产生深远影响！ 这个主题通常被初学者忽略，但是我们如何增加对象的错误会给未来的开发人员和管理员带来极大的麻烦。

In this article we will explore the different ways we can create and insert data into both permanent and temporary objects. Performance, syntax, documentation, and maintainability will be evaluated for each method. By delving deeper into this topic, we can improve database design, script quality, and build objects that are easier to upkeep and less likely to break due to maintenance or software releases.

在本文中，我们将探讨创建和向永久对象和临时对象插入数据的不同方法。 将针对每种方法评估性能，语法，文档和可维护性。 通过更深入地研究该主题，我们可以改善数据库设计，脚本质量，并构建易于维护且由于维护或软件版本而导致损坏的对象。

演示数据 (Demo Data)

All demos in this article will use new objects we create here. This will allow us full reign to customize, test, and break it independently of anything else we are working on.

本文中的所有演示都将使用我们在此处创建的新对象。 这将使我们能够独立于我们正在进行的任何其他事情而完全自定义，测试和破坏它。

The following is the TSQL to create a table called dbo.accounts:

以下是TSQL创建的名为dbo.accounts的表：

CREATE TABLE dbo.account
( account_id INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_account PRIMARY KEY CLUSTERED,
  account_name VARCHAR(100) NOT NULL,
  account_start_date DATE NOT NULL,
  account_address VARCHAR(1000) NOT NULL,
  account_type VARCHAR(10) NOT NULL,
  account_create_timestamp DATETIME NOT NULL,
    account_notes VARCHAR(500) NULL,
  is_active BIT NOT NULL);

This is a fairly simple table with an identity ID and some string/date columns for account data. As we work through this article, we will add and remove columns, as well as customize this further.

这是一个非常简单的表，带有标识ID和一些用于帐户数据的字符串/日期列。 在阅读本文时，我们将添加和删除列，并对此进行进一步的自定义。

使用显式列列表将数据插入SQL Server (Insert Data into SQL Server Using an Explicit Column List)

Let’s begin by diving straight into some of the simplest of syntaxes in T-SQL: The INSERT statement. The most common way to insert rows into a table is by doing so with an INSERT statement where we explicitly cite the entire column list prior to providing the values:

让我们开始直接研究T-SQL中最简单的语法：INSERT语句。 在表中插入行的最常见方法是使用INSERT语句，在提供值之前，我们明确引用了整个列列表：

INSERT INTO dbo.account
  (account_name, account_start_date, account_address, account_type, account_create_timestamp, account_notes, is_active)
VALUES
  ('Ed''s Account',
   '5/1/2019',
   'Ed''s Address',
   'TEST',
   GETUTCDATE(),
   'This is a test account to model this data.',
   0);

In this example, we provide a complete column list and use the VALUES syntax to list out scalar values to insert into the table. If desired, we can insert multiple rows via this syntax, separating each row by a comma.

在此示例中，我们提供了完整的列列表，并使用VALUES语法列出了要插入表中的标量值。 如果需要，我们可以通过这种语法插入多行，并用逗号分隔每一行。

We also have the option of omitting columns from the column and SELECT lists. This can be used for columns that allow NULL (and we want to be left NULL), or for columns that have default constraints defined on them (and we want the column to accept the default value). The following example shows an account insertion where we omit the account_notes column:

我们还可以选择从列和SELECT列表中省略列。 这可以用于允许NULL的列（并且我们希望保留NULL），或者用于在其上定义了默认约束的列（并且我们希望该列接受默认值）。 以下示例显示了一个帐户插入，其中我们省略了account_notes列：

INSERT INTO dbo.account
  (account_name, account_start_date, account_address, account_type, account_create_timestamp, is_active)
VALUES
  ('Initech',
   '2/19/1999',
   '4120 Freidrich Ln.',
   'LIVE',
   GETUTCDATE(),
   1);

After the two above insertions, we can view the resulting data and note that the results are what we expect:

在以上两次插入之后，我们可以查看结果数据并注意结果是我们期望的：

SQL Server allowed us to omit the account_notes column and in doing so assigned NULL in its place. Let’s add a default constraint to this column:

SQL Server允许我们忽略account_notes列，并在此位置分配NULL。 让我们向此列添加默认约束：

ALTER TABLE dbo.account ADD CONSTRAINT DF_account_account_notes DEFAULT ('NONE PROVIDED') FOR account_notes;

With a default constraint on the column, we may test another INSERT where we intentionally leave out the account_notes column:

在该列具有默认约束的情况下，我们可以测试另一个INSERT，在其中我们有意省略account_notes列：

INSERT INTO dbo.account
  (account_name, account_start_date, account_address, account_type, account_create_timestamp, is_active)
SELECT
  'Dinosaur Corp',
  '1/1/2003',
  'The Triassic Time Period',
  'DEMO',
  GETUTCDATE(),
  1;

The results show us how the new row looks in our table:

结果向我们展示了表格中新行的外观：

We can see that the default value from the constraint was applied to account_notes, as expected. Creating a default constraint can be useful for ensuring that a column can be made NOT NULL and always be assigned a value. It is also useful when we wish to have a column that typically is not assigned a value, but requires one for an application or reporting purpose. A default constraint should never be used to generate placeholder, fake, or obfuscated data. For example, -1 is a poor choice for an integer column and 1/1/1900 is a lousy choice for a date column as each provides confusing meaning that is not intuitive to a developer or someone consuming this data.

我们可以看到，约束的默认值已按预期方式应用于account_notes 。 创建默认约束对于确保可以将列设置为NOT NULL并始终为其分配值非常有用。 当我们希望有一列通常没有分配值，但出于应用程序或报告目的而需要一个值的列时，它也很有用。 绝对不要使用默认约束来生成占位符，伪造或混淆的数据。 例如，对于整数列，-1是一个糟糕的选择，对于日期列，1/1/1900是一个糟糕的选择，因为每个列都提供了令人困惑的含义，这对于开发人员或使用此数据的人而言并不直观。

The primary benefit of inserting data with an explicit column list are that you document exactly what columns are being populated, and what data is being put into each column. If a column is left off the list, then it will be made NULL. If a NOT NULL column with no default constraint is left off of the list, an error will be thrown, similar to this:

使用显式列列表插入数据的主要好处是，您可以准确地记录正在填充的列以及将哪些数据放入每个列。 如果列不在列表中，则将其设为NULL。 如果将没有默认约束的NOT NULL列从列表中删除，则将引发错误，类似于：

Similarly, if you accidentally leave off a column from the column list, you’ll get this error:

同样，如果您不小心从列列表中删除了某个列，则会收到此错误：

As a result, the explicitly provided column list makes it hard to accidentally leave columns out.

结果，显式提供的列列表使意外地遗漏列变得很困难。

This syntax has a downside, though, and that is maintainability in scenarios where table schema changes often and there is a desire to always SELECT *. If you are dumping data to an output table and do not care about column order, typing, or quantity, then having to always adjust the column list to match the SELECT details may be a hassle and not worth the effort.

但是，此语法有一个缺点，那就是在表模式经常更改并且希望始终选择SELECT *的情况下具有可维护性。 如果要将数据转储到输出表中，而不关心列的顺序，类型或数量，那么必须始终调整列列表以使其与SELECT详细信息相匹配可能是一件麻烦的事，不值得花精力。

在没有显式列列表的情况下将数据插入SQL Server (Insert Data into SQL Server Without an Explicit Column List)

This leads us into the alternative way to insert data into an existing table, which is to do so without a column list. An insert of this form will look like this:

这使我们进入了将数据插入到现有表中的另一种方法，即没有列列表。 这种形式的插入将如下所示：

INSERT INTO dbo.account
SELECT
  'The Pokemon Company',
  '4/23/1998',
  'Roppongi Hills Mori Tower 8F, Tokyo, Japan',
  'LIVE',
  GETUTCDATE(),
  'Very valuable.  They make all the Pokemon!',
   1;

This statement executes successfully, despite no column list being provided. When SQL Server binds this query to its underlying objects, it will pull the column list in order and apply it to the data being inserted. If everything matches up, then we’re good to go, otherwise we will receive an error similar to the two we saw earlier that indicate column lists that do not match up.

尽管未提供任何列列表，该语句仍成功执行。 当SQL Server将此查询绑定到其基础对象时，它将按顺序拉出列列表，并将其应用于要插入的数据。 如果所有内容都匹配，那么我们就走了，否则，我们将收到与我们之前看到的两个错误类似的错误，该错误指示不匹配的列列表。

The upside (if we wish to call it an upside) of this approach is that it is fast and requires less upkeep as there’s no need to write and maintain a list of columns with the insert. The downside is that if you mismatch columns, you may get a different sort of error message. Consider the following query:

这种方法的好处（如果我们希望称其为好处）是它速度快并且需要较少的维护，因为无需使用插入方法编写和维护列列表。 缺点是，如果您不匹配列，则可能会收到另一种错误消息。 考虑以下查询：

INSERT INTO dbo.account
SELECT
  'The Pokemon Company',
  '4/23/1998',
  'Roppongi Hills Mori Tower 8F, Tokyo, Japan',
  GETUTCDATE(),
  'Very valuable.  They make all the Pokemon!',
   0;

When executed, we get a new error message:

执行后，我们会收到一条新的错误消息：

In this scenario, we left out a column, but since this table includes NULLable columns, SQL Server tried to match up the table’s columns anyway using the data we provided, but was unable to make a meaningful match.

在这种情况下，我们省略了一个列，但是由于该表包含可为空的列，因此SQL Server仍然尝试使用我们提供的数据来匹配该表的列，但无法进行有意义的匹配。

Because of the potential for confusing errors and the inability to easily match up columns to data, inserting into a table without providing a column list is not seen as a best practice and should be avoided whenever possible. I would recommend including a column list in all INSERT statements unless you happen to have a very unique use-case in which the convenience of the simpler syntax outweighs the risk of future code breaking if the table’s schema changes.

由于可能会导致错误混淆，并且无法轻松地将列与数据匹配，因此，不提供列列表而插入表中不是最佳实践，应尽可能避免。 我建议在所有INSERT语句中都包含一个列列表，除非您碰巧遇到一个非常独特的用例，在这种用例中，如果表的模式发生更改，使用简单语法的便利性会超过将来破坏代码的风险。

In addition, the column list provides clear documentation to the user as to what is being inserted and which columns the data will be going into. We can improve upon this further if we wish by adding aliases to all inserted columns:

另外，列列表为用户提供了关于要插入的内容以及数据将进入的列的清晰文档。 如果我们愿意，可以通过向所有插入的列添加别名来进一步改善这一点：

INSERT INTO dbo.account
  (account_name, account_start_date, account_address, account_type, account_create_timestamp, account_notes, is_active)
SELECT
  'Microsoft' AS account_name,
  '4/4/1975' AS account_start_date,
  'One Microsoft Way in Redmond, Washington' AS account_address,
  'LIVE' AS account_type,
  GETUTCDATE() AS account_start_date,
  'They make SQL Server.  Thanks!' AS account_notes,
   1 AS is_active;

Adding column aliases may seem like overkill, but when working with longer INSERT statements, dynamic SQL, or complex queries, the added documentation can greatly aid in writing, modifying, and troubleshooting those queries. You can also test your insert statement by highlighting the SELECT list only and executing it, which returns values along with the handy column headers:

添加列别名似乎有些过头，但是当使用更长的INSERT语句，动态SQL或复杂查询时，添加的文档可以极大地帮助编写，修改和故障排除这些查询。 您还可以通过仅突出显示SELECT列表并执行它来测试您的insert语句，该列表将返回值以及方便的列标题：

Alternatively, you can provide that documentation in comments:

或者，您可以在注释中提供该文档：

INSERT INTO dbo.account
  (account_name, account_start_date, account_address, account_type, account_start_date, account_notes, is_active)
SELECT
  'Microsoft', -- account_name
  '4/4/1975', -- account_start_date
  'One Microsoft Way in Redmond, Washington', -- account_address
  'LIVE', -- account_type
  GETUTCDATE(), -- account_start_date
  'They make SQL Server.  Thanks!', -- account_notes
   1; -- is_active

This can be a nice way to document without adjusting the query. The only downside is that you lose the handy column names that were demonstrated above with the test SELECT. Both are worth the extra 30 seconds to include in your code as they will save far more time in the future when the time comes to modify or troubleshoot that code.

这可能是一种无需调整查询即可记录文档的好方法。 唯一的缺点是您丢失了上面用测试SELECT演示的方便的列名。 两者都值得花30秒才能包含在您的代码中，因为将来在修改或排除该代码故障时，它们将节省更多时间。

使用SELECT INTO将数据插入SQL Server (Insert Data into SQL Server with SELECT INTO)

It is possible to create a new table object and SELECT data into it as a part of a single statement. This can be a convenient way to avoid the hassle of defining a table up-front and enumerating column names. Typically this is used to populate temporary tables, but it could be used for permanent tables if a scenario called for it. SELECT INTO may not be used to automatically create table variables.

作为单个语句的一部分，可以创建一个新的表对象并将SELECT数据放入其中。 这可能是避免麻烦的方法，它避免了预先定义表和枚举列名的麻烦。 通常，它用于填充临时表，但是如果需要，可以将其用于永久表。 SELECT INTO可能不用于自动创建表变量。

The query below shows how we can accomplish this task quickly and efficiently:

以下查询显示了我们如何快速有效地完成此任务：

SELECT
  'Ed Pollack' AS developer_name,
  'SQL Server 2019 CTP1' AS database_engine_of_choice,
  'Pizza' AS food_choice,
  10 AS spice_level
INTO #developer_info;

When executed, a temporary table will be created on-the-fly with the column names provided. When we query the table, we get the following results:

执行后，将使用提供的列名即时创建一个临时表。 查询表时，将得到以下结果：

The data types for the columns are automatically chosen by SQL Server. We can verify them by querying TempDB as follows:

列的数据类型由SQL Server自动选择。 我们可以通过查询TempDB来验证它们，如下所示：

SELECT
  tables.name AS TableName,
  columns.name AS ColumnName,
  columns.max_length AS ColumnLength,
  types.name AS TypeName
FROM TempDB.sys.tables
INNER JOIN TempDB.sys.columns
ON tables.object_id = columns.object_id
INNER JOIN TempDB.sys.types
ON types.user_type_id = columns.user_type_id
WHERE tables.name LIKE '#developer_info%';

The results show columns that were sized to meet the minimum needs of the data that I inserted:

结果显示列的大小可以满足我插入的数据的最低需求：

Had I inserted multiple rows, then the column sizes would match the smallest sizes needed to fit all of that data. This is amazingly convenient assuming that we do not need to add additional data to the temporary table later. If we do, we would need to ensure that the new data does not exceed the column sizes that have already been defined.

如果我插入了多行，那么列大小将与适合所有数据所需的最小大小匹配。 假设我们以后不需要将其他数据添加到临时表中，这将非常方便。 如果这样做，我们将需要确保新数据不超过已定义的列大小。

The SELECT INTO syntax provides a huge convenience when we want to insert data into a table that may vary greatly from execution-to-execution, or that may undergo schema changes often enough so as to make the maintenance of column lists challenging.

当我们希望将数据插入到一个表中时，如果执行与执行之间的差异可能很大，或者可能经常进行架构更改，从而使维护列列表变得颇具挑战性，那么SELECT INTO语法将为您提供极大的便利。

You can control data types if you wish. Let’s repeat our example above using the following TSQL:

您可以根据需要控制数据类型。 让我们使用以下TSQL重复上面的示例：

SELECT
  CAST('Ed Pollack' AS VARCHAR(50)) AS developer_name,
  CAST('SQL Server 2019 CTP1' AS VARCHAR(30)) AS database_engine_of_choice,
  CAST('Pizza' AS VARCHAR(25)) AS food_choice,
  CAST(10 AS DECIMAL(5,2)) AS spice_level
INTO #developer_info;

When we consult with system views, we can confirm that the data types have been defined as we wanted them and not as the minimal sizes required to support the data that I created:

在咨询系统视图时，我们可以确认已根据需要定义了数据类型，而不是支持我创建的数据所需的最小大小：

The primary downside of using the SELECT INTO syntax is that the resulting table will have no indexes or constraints defined on it. If any additional structure is required for the table, it will need to be added after-the-fact. For scenarios where this is not needed, then SELECT INTO provides a very fast and convenient syntax that can shorten code and improve maintainability by reducing the number of places that require change when the data’s underlying schema or definition undergo change.

使用SELECT INTO语法的主要缺点是，结果表将没有定义索引或约束。 如果表需要任何其他结构，则需要在事后添加。 对于不需要的情况，SELECT INTO提供了一种非常快速便捷的语法，可以通过减少当数据的基础架构或定义发生更改时需要更改的位置数来缩短代码并提高可维护性。

SELECT INTO can also be used for a generic SELECT statement with no additional definitions, such as this:

SELECT INTO也可以用于没有其他定义的通用SELECT语句，例如：

SELECT
  *
INTO dbo.account_backup
FROM dbo.account;

In a very short TSQL statement, we created a new permanent table and inserted all of the contents of dbo.account into it. Note that when this syntax is used, the data types in the resulting table will match the source table as they are currently defined.

在很短的TSQL语句中，我们创建了一个新的永久表，并将dbo.account的所有内容插入其中。 请注意，使用此语法时，结果表中的数据类型将与源表匹配，因为它们当前已定义。

通过存储过程或动态SQL插入Data SQL Server (Insert Data SQL Server via Stored Procedure or Dynamic SQL)

Stored procedures are often used to return data to applications, reports, or additional TSQL scripts for processing. The results of any stored procedure can be inserted directly into a predefined table like this:

存储过程通常用于将数据返回到应用程序，报告或其他TSQL脚本进行处理。 任何存储过程的结果都可以直接插入到预定义表中，如下所示：

CREATE TABLE #temp
( SPID VARCHAR(100),
  STATUS VARCHAR(100),
  LOGIN VARCHAR(100),
  hostname VARCHAR(100),
  blkby VARCHAR(100),
  dbname VARCHAR(100),
  command VARCHAR(100),
  cputime VARCHAR(100),
  diskit VARCHAR(100),
  latbatch VARCHAR(100),
  programname VARCHAR(100),
  spid2 VARCHAR(100),
  requestid VARCHAR(100))
INSERT INTO #temp
EXEC sp_who2;
 
SELECT * FROM #temp
WHERE dbname = 'AdventureWorks2016CTP3';
DROP TABLE #temp;

This monitoring script will execute sp_who2 to return a list of current processes in a given database. By default, this stored procedure returns all sessions, though parameters can be supplied to filter by login or session ID. To filter by database, though, would otherwise require returning all data and then manually removing the irrelevant rows. By creating a temporary table up front and inserting the results directly into it, we are then freed up to filter the result set by whatever criteria we wish. Any table may be used for this purpose, including permanent tables, as well as table variables.

该监视脚本将执行sp_who2以返回给定数据库中当前进程的列表。 默认情况下，此存储过程返回所有会话，尽管可以提供参数以按登录名或会话ID进行过滤。 但是，要按数据库进行过滤，将需要返回所有数据，然后手动删除不相关的行。 通过在前面创建一个临时表并将结果直接插入到表中，然后释放我们以根据所需条件筛选结果集。 任何表都可以用于此目的，包括永久表以及表变量。

The same syntax can be used to insert the results of dynamic SQL into a table, like this:

可以使用相同的语法将动态SQL的结果插入表中，如下所示：

DECLARE @sql_command NVARCHAR(MAX);
DECLARE @account_type SYSNAME = 'LIVE';
SELECT @sql_command = '
  SELECT
    account_id
  FROM dbo.account
  WHERE account_type = ''' + @account_type + ''';';
CREATE TABLE #id_list
  (account_id INT NOT NULL PRIMARY KEY CLUSTERED);
 
INSERT INTO #id_list
  (account_id)
EXEC sp_executesql @sql_command;
 
SELECT * FROM #id_list;
DROP TABLE #id_list;

The end result of this code is that we use sp_executesql to execute dynamic SQL, placing the results directly into a temporary table.

该代码的最终结果是，我们使用sp_executesql执行动态SQL，并将结果直接放入临时表中。

In both of these scenarios, we were able to execute procedural TSQL and insert the results directly into an existing table. SELECT INTO is not supported with this syntax, but everything else we have discussed so far does work with this convention.

在这两种情况下，我们都能够执行过程性TSQL并将结果直接插入到现有表中。 SELECT INTO不受此语法支持，但是到目前为止，我们讨论的所有其他内容都适用于此约定。

使用OUTPUT INSERTED插入Data SQL Server (Insert Data SQL Server with OUTPUT INSERTED)

A bonus way we can generate data is via an existing statement. When we execute any write operation, we may output data from before or after the change to another table. Here is an example of how this looks:

我们生成数据的一种额外方式是通过现有语句。 当我们执行任何写操作时，我们可能会将更改前后的数据输出到另一个表。 这是看起来的例子：

CREATE TABLE #account_ids
  (account_id INT NOT NULL PRIMARY KEY CLUSTERED);
 
UPDATE account
  SET is_active = 1
OUTPUT INSERTED.account_id
INTO #account_ids
FROM dbo.account
WHERE account_type = 'LIVE';
 
SELECT * FROM #account_ids;
 
DROP TABLE #account_ids;

The goal above is to update all accounts of type “LIVE” to be active. We also want to return the account_id for each account that was updated. Using OUTPUT INSERTED allows us to accomplish both tasks in a single set-based solution. The results show us which IDs were affected by the update statement:

上面的目标是将所有“ LIVE”类型的帐户更新为活动状态。 我们还想返回每个已更新帐户的account_id。 使用OUTPUT INSERTED可以使我们在单个基于集合的解决方案中完成两项任务。 结果向我们显示了哪些ID受update语句影响：

INSERTED will contain all columns in the table as they appear after changes have been applied. Similarly, DELETED will contain the previous versions. We can mix and match these for maximum effect:

应用更改后，INSERTTED将包含表中的所有列。 同样，DELETED将包含以前的版本。 我们可以将它们混合并匹配以达到最大效果：

CREATE TABLE #account_ids
  (account_id INT NOT NULL PRIMARY KEY CLUSTERED, is_active_previous BIT NOT NULL, is_active_current BIT NOT NULL);
 
UPDATE account
  SET is_active = 0
OUTPUT INSERTED.account_id,
     DELETED.is_active,
     INSERTED.is_active
INTO #account_ids
FROM dbo.account
WHERE account_type = 'LIVE';
 
SELECT * FROM #account_ids;
 
DROP TABLE #account_ids;

The results show that we not only captured the account IDs, but also the previous and new values for the is_active flag. This is a huge convenience as we can use OUTPUT INSERTED in INSERT, DELETE, UPDATE, and MERGE statements to quickly pull before & after data for use in additional processing.

结果表明，我们不仅捕获了帐户ID，而且还捕获了is_active标志的先前值和新值。 这是极大的便利，因为我们可以在INSERT，DELETE，UPDATE和MERGE语句中使用OUTPUT INSERTED来快速提取数据之前和之后以进行其他处理。

This is a far superior solution to iteration and/or using SCOPE_IDENTITY() and is the only easy way to capture data in this fashion with so little code. OUTPUT INSERTED is a great method for getting before & after data from DML statements. It is also useful for collecting a list of rows that were altered in a given TSQL statement, so we can take additional actions or report on them as needed.

对于迭代和/或使用SCOPE_IDENTITY（）而言 ，这是一种非常出色的解决方案，并且是用这种方式以很少的代码捕获数据的唯一简便方法。 OUTPUT INSERTED是一种从DML语句获取数据前后的好方法。 这对于收集在给定的TSQL语句中已更改的行的列表也很有用，因此我们可以采取其他措施或根据需要对其进行报告。

哪种方法最好？ (Which Method is Best?)

With a variety of methods to insert data into SQL Server available, the first question we will ask is which syntax should we use? The answer is going to depend on your use-case and specifically what is most important to a given application. To summarize our work so far:

通过将数据插入SQL Server的多种方法，我们将要问的第一个问题是应使用哪种语法？ 答案将取决于您的用例，尤其是对给定应用程序最重要的是什么。 总结到目前为止的工作：

Use an INSERT with an explicit column list for applications where column lists, inputs, and outputs do not change often. These are scenarios where change typically consist of column additions or alterations resulting from software releases. The column lists also add a layer of protection against logical errors if a column is added, removed, or altered without the INSERT statement also being updated. An error being thrown is a far better outcome than data quietly being handled incorrectly. This syntax is generally considered a best practice as it provides both documentation and protection against inadvertent mistakes should the schema change in the future.

对于列列表，输入和输出不经常更改的应用程序，请使用带有显式列列表的INSERT。 在这些情况下，更改通常由软件发行版中列的添加或更改组成。 如果在不更新INSERT语句的情况下添加，删除或更改了列，则列列表还为逻辑错误提供了一层保护。 与错误地静默处理错误数据相比，抛出错误要好得多。 这种语法通常被认为是最佳实践，因为如果将来更改架构，它可以提供文档和针对意外错误的保护。

An INSERT with no column list carves out a niche in scenarios where the columns are unknown or frequently changing. This might be useful in ETL, reporting, or scenarios involving transient data when the data is unstructured. Despite that possible application, I would lean towards using SELECT INTO for these applications as they provide a bit more safety against inadvertent mistakes with your data. An alternative to cover the need to insert into a pre-existing table would be to use SELECT INTO to create a temporary data structure and then insert from that temporary data into the permanent table using a formal column list.

没有列列表的INSERT在列未知或频繁更改的情况下会占据一席之地。 当数据是非结构化的时，这在ETL，报告或涉及瞬态数据的场景中可能很有用。 尽管有可能的应用程序，但我倾向于将SELECT INTO用于这些应用程序，因为它们为防止数据意外错误提供了更高的安全性。 满足需要插入到现有表中的另一种方法是使用SELECT INTO创建一个临时数据结构，然后使用正式列列表将该临时数据插入到永久表中。

SELECT INTO provides us the ability to quickly create a new table and dump data into it with very little setup or coding. For small data sets or those that are going to be scanned in their entirety, this is a great way to move data with minimal TSQL and maintenance. The primary downside is the inability to include indexes or constraints on the table until after it is created. This syntax must create a new table, which lends itself towards using temporary tables as a target to insert data to.

SELECT INTO使我们能够快速创建新表并将数据转储到该表中，而只需进行很少的设置或编码。 对于小型数据集或将要整体扫描的数据集，这是一种以最少的TSQL和维护来移动数据的好方法。 主要缺点是直到创建表后才能在表中包括索引或约束。 此语法必须创建一个新表，这有助于使用临时表作为插入数据的目标。

OUTPUT INSERTED allows us to return data from before, after, or before and after a DML statement. This is a hugely useful syntax and one that has no intrinsic downsides. It is a great alternative to iteration, functions, or triggers that may attempt to accomplish the same tasks. The syntax is relatively simple and can be applied to any data elements prior to or after a given change. You may even include data that is not directly affected by a DML statement, but was joined for use in the query!

OUTPUT INSERTED允许我们从DML语句之前，之后，之前或之后返回数据。 这是一种非常有用的语法，并且没有内在的缺点。 它是可能试图完成相同任务的迭代，函数或触发器的绝佳替代方案。 语法相对简单，可以在给定更改之前或之后应用于任何数据元素。 您甚至可以包含不受DML语句直接影响的数据，但这些数据已加入以便在查询中使用！

结论 (Conclusion)

There are many ways to insert data into SQL Server, but not all were created equal. Picking the right syntax can have a significant impact on performance, documentation, and maintainability. This article provides a comparison of a variety of syntaxes, as well as the pros, cons, and demos of each one.

有很多方法可以将数据插入SQL Server，但并非所有方法都是一样的。 选择正确的语法可能会对性能，文档和可维护性产生重大影响。 本文提供了多种语法的比较，以及每种语法的优缺点。

Always consider your application when writing code and adjust your style based on how often schema and code are expected to change. Schema and applications that are static and rarely changed can afford to have hard-coded INSERT statements with columns, aliases, and/or reference documentation to make them easy to read and maintain.

编写代码时，请始终考虑您的应用程序，并根据期望更改架构和代码的频率来调整样式。 静态且很少更改的架构和应用程序可以使用带有列，别名和/或参考文档的硬编码INSERT语句，以使其易于阅读和维护。

For schema or business needs that changes often, SELECT INTO or the omission of the column list from an INSERT may allow for more fluidity in places where code would greatly benefit from it.

对于经常变化的模式或业务需求，SELECT INTO或INSERT省略了列列表可能会在代码将大大受益的地方提供更大的流动性。

Having a variety of options at our disposal allows us to make better decisions when smart choices are needed and will help us write better code that will make future developers appreciate us that much more as it is updated and maintained!

我们拥有多种选择，可让我们在需要明智的选择时做出更好的决策，并有助于我们编写更好的代码，这将使未来的开发人员对我们的更新和维护更加感激！

参考资料和进一步阅读 (References and Further Reading)

• OUTPUT clauseOUTPUT子句的一般用法
• how to use system catalog views from Microsoft’s documentation如何使用系统目录视图的演示
• INSERT statementINSERT语句上的广泛文档

翻译自: https://www.sqlshack.com/methods-to-insert-data-into-sql-server/