CREATE VIEW SQL：通过SQL Server中的视图插入数据

This is the third article in a series of learning the CREATE VIEW SQL statement. So far, I’d say that we’re comfortable and familiar with the syntax, and we’ve learned how to create and modify views. In this part, we’ll continue to work on views using the sample database and data that we created so far.

这是学习CREATE VIEW SQL语句系列的第三篇文章。到目前为止，我想说我们对语法很熟悉并且很熟悉，并且我们已经学习了如何创建和修改视图。在这一部分中，我们将继续使用迄今为止创建的示例数据库和数据来处理视图。

To briefly summarize the series, in the first part about Creating views in SQL Server, the idea was to get familiar with the CREATE VIEW SQL syntax, all the different things we can do with views, and creating a really basic view.

为了简要总结该系列，在关于在SQL Server中创建视图的第一部分中，其想法是熟悉CREATE VIEW SQL语法，我们可以使用视图进行的所有不同操作以及创建一个真正的基本视图。

In the second part about Modifying views in SQL Server, we upped the difficulty a little bit and created a more complex view with aggregates in it. Furthermore, we got familiar with the ALTER VIEW statement used to change the output by changing the definition and structure of a query.

在有关在SQL Server中修改视图的第二部分中，我们稍微提高了难度，并创建了一个更复杂的视图，其中包含聚合。此外，我们熟悉了用于通过更改查询的定义和结构来更改输出的ALTER VIEW语句。

介绍 (Introduction)

Although this article can be read independently from the first two, it’s highly advisable to head over and read the previous two parts to get the full picture and because it will be easier to follow along.

尽管可以从前两部分中独立阅读本文，但还是建议您先阅读并阅读前两部分以获取完整信息，因为这样做会更容易。

Now it’s time to start using Data Manipulation Language (DML) that is used to manipulate data itself and see how we can insert data into a table through a view.

现在是时候开始使用数据操纵语言（DML）了，该语言用于操纵数据本身，并了解如何通过视图将数据插入表中。

However, before we actually insert data through a view, let’s see how we can rename a view. I also want to show you one neat thing that we can do WITH CHECK OPTION which is a part of the CREATE VIEW SQL syntax. This option can be useful when inserting data through a view which you’ll see later in this article.

但是，在实际通过视图插入数据之前，让我们看看如何重命名视图。我还想向您展示我们可以使用WITH CHECK OPTION进行的一件整洁的事情，它是CREATE VIEW SQL语法的一部分。通过视图插入数据时，此选项很有用，您将在本文后面看到。

重命名视图 (Renaming views)

Views are renamed using the sp_rename system stored procedure. By definition, this SP is used for changing the name of a user-created object in the current database. Having said that, changing any part of an object name, including views, can break scripts and dependencies, which is why it’s not recommended to use this statement to rename views or any other user-created object. The smart thing to do would be to drop the object and re-create it with the new name using the CREATE VIEW SQL statement.

使用sp_rename系统存储过程将视图重命名。根据定义，此SP用于更改当前数据库中用户创建的对象的名称。话虽如此，更改对象名称的任何部分（包括视图）都可能破坏脚本和依赖关系，这就是为什么不建议使用此语句来重命名视图或任何其他用户创建的对象的原因。明智的做法是删除对象，然后使用CREATE VIEW SQL语句以新名称重新创建该对象。

使用第三方软件解决方案重命名视图 (Renaming views using a third-party software solution)

If you want to play it safe, I’d suggest a third-party software solution for safely renaming SQL objects. I, personally, use free SQL Server Management Studio and Visual Studio add-ins for SQL Server called ApexSQL Search and ApexSQL Refactor. Both add-ins have a feature called Safe rename that renames tables, procedures, views, functions, and columns without breaking dependencies.

如果您想安全地使用它，我建议使用第三方软件解决方案来安全地重命名SQL对象。我个人使用免费SQL Server Management Studio和Visual Studio加载项（用于ApexSQL Search和ApexSQL Refactor）来访问SQL Server。这两个加载项均具有称为“ 安全重命名”的功能，该功能可以在不破坏依赖关系的情况下重命名表，过程，视图，函数和列。

For detailed information about renaming SQL objects using this feature, see the following articles:

有关使用此功能重命名SQL对象的详细信息，请参阅以下文章：

How to rename database objects in a SQL Server database safely – Part 1 如何安全地重命名SQL Server数据库中的数据库对象-第1部分
How to rename database objects in a SQL Server safely – Part 2 如何在SQL Server中安全地重命名数据库对象–第2部分

This is a series about using T-SQL, so we’re going to do it using the stored procedure mentioned above. Make sure that you’re are connected to the SQLShackDB database or specify the database name in the USE statement to avoid getting errors from SQL Server. The example below changes the database context to the SQLShackDB database and changes the view’s name:

这是有关使用T-SQL的系列文章，因此我们将使用上述存储过程来完成它。确保已连接到SQLShackDB数据库，或在USE语句中指定数据库名称，以避免从SQL Server中获取错误。下面的示例将数据库上下文更改为SQLShackDB数据库，并更改视图的名称：

USE SQLShackDB;
GO
sp_rename 
 'vEmployeesWithSales', 
 'vEmployees';

If we head over to Object Explorer and refresh the Views folder, we should see that the name changed to vEmployees. There’s also a warning from SQL Server about changing any part of an object name that I’ve mentioned earlier:

如果转到Object Explorer并刷新Views文件夹，则应该看到名称已更改为vEmployees 。 SQL Server还警告您更改我前面提到的对象名称的任何部分：

Now, let’s completely change the definition of the view by executing the code from below:

现在，让我们从下面执行代码来完全更改视图的定义：

USE SQLShackDB;
GO
ALTER VIEW vEmployees
AS
     SELECT *
     FROM Employees;
GO

We could have just dropped the view and use the CREATE VIEW SQL statement to re-create it with the new definition, but we can’t break script and dependencies with this simple view, so it’s safe to just rename it using the stored procedure.

我们可以删除视图并使用CREATE VIEW SQL语句以新定义重新创建它，但是我们不能使用此简单视图破坏脚本和依赖关系，因此使用存储过程对其进行重命名是安全的。

I can’t help but notice the name of the view underlined in red. We’ve all seen this at some point. This indicates an error in SSMS, in this particular case about an invalid object name, but it’s not:

我不禁注意到红色下划线的视图名称。我们都已经看到了这一点。这表明SSMS中存在错误，在这种特殊情况下，对象名称无效，但不是：

This is coming from SSMS’s IntelliSense. We just changed the name of the view and the metadata has not been updated yet. The same would have happened to the CREATE VIEW SQL statement. Right after the creation of an object, when queried, it’s very likely for the view as the source to be underlined in red until the cache is refreshed. An easy way to fix this, although it’s not an actual error (the above script can be executed without throwing an error), is to refresh the IntelliSense’s cache manually.

这来自SSMS的IntelliSense。我们只是更改了视图的名称，并且元数据尚未更新。 CREATE VIEW SQL语句也会发生相同的情况。在创建对象之后，当查询该对象时，很可能会将视图作为源用红色下划线，直到刷新缓存为止。解决此问题的简便方法是手动刷新IntelliSense的缓存，尽管这不是实际错误（可以在不引发错误的情况下执行上述脚本）。

In SSMS, go to Edit on the main menu. Expand the Intellisense options and select Refresh Local Cache as shown below:

在SSMS中，转到主菜单上的“ 编辑 ”。展开Intellisense选项，然后选择“ 刷新本地缓存” ，如下所示：

As soon as we refresh the cache, metadata is up-to-date (sometimes it takes a couple of seconds), and Intellisense removes the red squiggles line.

刷新缓存后，元数据是最新的（有时需要几秒钟），并且Intellisense会删除红色的花体行。

So, let’s move on and execute the ALTER VIEW statement to change also the definition of the view to a simple SELECT statement that retrieves all the columns from the Employees table:

因此，让我们继续执行ALTER VIEW语句，将视图的定义也更改为一个简单的SELECT语句，该语句从Employees表中检索所有列：

So, if we query the vEmployees view now, it should return all columns from the Employees table as shown below:

因此，如果我们现在查询vEmployees视图，它将返回Employees表中的所有列，如下所示：

USE SQLShackDB;
GO
 
SELECT * FROM vEmployees;

通过视图修改数据 (Data modifications through views)

Finally, let’s see how we can do data modifications through the vEmployees view. This is a very neat thing that we can do. Just think about it. We started with the CREATE VIEW SQL statement, we then created a very simple view, and now we’re going to use that view to insert a record into our Employees table.

最后，让我们看看如何通过vEmployees视图进行数据修改。这是我们可以做的非常整洁的事情。考虑一下。我们从CREATE VIEW SQL语句开始，然后创建一个非常简单的视图，现在我们将使用该视图将记录插入到Employees表中。

Let’s say that we need to insert an employee through our view. The below code is just an example of inserting data through a view using the SELECT statement:

假设我们需要通过视图插入一名员工。以下代码只是使用SELECT语句通过视图插入数据的示例：

USE SQLShackDB;
GO
 
INSERT INTO vEmployees
       SELECT 3, 
              'Bojan', 
              NULL, 
              'Petrovic', 
              'Author', 
              '1/1/2017', 
              2080, 
              '100000.00';

To explain the INSERT INTO statement, I’m simply using SELECT after the name of our view, which is a very simple way to insert data into tables as we’re inserting new data based on the result of the SELECT statement.

为了解释INSERT INTO语句，我只是在视图名称后使用SELECT ，这是在根据SELECT语句的结果插入新数据时将数据插入表中的一种非常简单的方法。

The vEmployees view has 8 columns. I like to expand the Columns folder of the view in Object Explorer just to see which columns cannot be a nullable value and also because it’s easier to write the SELECT statement and match all values one-by-one. After executing the above script, we should have a “1 row affected” message returned meaning that the new record went into the table successfully:

vEmployees视图具有8列。我想在“ 对象资源管理器”中扩展视图的“ 列”文件夹，只是为了查看哪些列不能为可为空的值，而且还因为编写SELECT语句并逐一匹配所有值更加容易。执行完上述脚本后，我们应该返回“ 1行受影响”消息，这意味着新记录已成功进入表中：

How, if we check the content of the Employees table, we should see the newly inserted record as shown below:

如何检查Employees表的内容，如何看到新插入的记录，如下所示：

As can be seen, we can do data modifications through views. Pretty cool, right? Now, let’s take our journey a bit further and see how we can use WITH CHECK OPTION, which is a part of the CREATE VIEW SQL syntax.

可以看出，我们可以通过视图进行数据修改。很酷吧？现在，让我们更进一步，看看如何使用WITH CHECK OPTION，它是CREATE VIEW SQL语法的一部分。

在视图中使用WITH CHECK OPTION (Using WITH CHECK OPTION in views)

Let’s see how the WITH CHECK OPTION comes into play. We are going to use the same ALTER VIEW statement from the beginning of this article to add this option to our view or you could just drop it and use the CREATE VIEW SQL statement to re-create it with a new definition. It’s totally up to you. Whatever you prefer is fine:

让我们看看“ WITH CHECK OPTION”如何发挥作用。我们将从本文开始使用相同的ALTER VIEW语句将该选项添加到我们的视图中，或者您可以将其删除并使用CREATE VIEW SQL语句以新定义重新创建它。这完全取决于您。无论您喜欢什么都可以：

USE SQLShackDB;
GO
 
ALTER VIEW vEmployees
AS
     SELECT *
     FROM Employees
     WHERE Title = 'Sales Representative'
 
WITH CHECK OPTION;
 
GO

As you can see, we also added a WHERE clause. Once executed, we should get the message that command executed successfully, which means that the view now has a new definition including the WITH CHECK OPTION:

如您所见，我们还添加了WHERE子句。一旦执行，我们应该得到命令成功执行的消息，这意味着视图现在有了一个新的定义，包括WITH CHECK OPTION ：

So, what the WITH CHECK OPTION does in SQL Server is easily explained in the following use case. Let’s say that we want to insert another record using the script from the previous example with only EmployeeID changed to 4:

因此，在以下用例中很容易解释WITH WITH CHECK OPTION在SQL Server中的作用。假设我们要使用上一个示例中的脚本插入另一条记录，只将EmployeeID更改为4 ：

USE SQLShackDB;
GO
 
INSERT INTO vEmployees
       SELECT 4, 
              'Bojan', 
              NULL, 
              'Petrovic', 
              'Author', 
              '1/1/2017', 
              2080, 
              '100000.00';

Let’s execute this command and see what happens. You’ll see a message that the statement has been terminated as shown below:

让我们执行此命令，看看会发生什么。您将看到一条消息，表明该语句已终止，如下所示：

Here’s the full SQL Server return message that cannot be seen in the shot above:

这是上面的快照中看不到的完整SQL Server返回消息：

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

尝试插入或更新失败，因为目标视图要么指定了WITH CHECK OPTION，要么跨越了一个指定WITH CHECK OPTION的视图，并且该操作产生的一个或多个行不符合CHECK OPTION约束。

What this SQL Server error message means is that any inserted data through our view should follow the condition in the WHERE clause. In this particular case, only records with condition Title = ‘Sales Representative’ met would have been updated or inserted.

此SQL Server错误消息的意思是，通过我们的视图插入的任何数据都应遵循WHERE子句中的条件。在这种情况下，只有满足条件Title ='Sales Representative'的记录才会被更新或插入。

So, if we change the above script and put Sales Representative instead of Author for the Title column, re-execute the script, it will work just fine. Now, if we query the Employees table, it should return two identical records:

因此，如果我们更改上述脚本，并在“ 标题”列中放置“ 销售代表”而不是“ 作者 ”，请重新执行该脚本，它将正常工作。现在，如果我们查询Employees表，它将返回两个相同的记录：

Bear in mind that if we query the vEmployees view, it should return only the Sales Representative record because of the condition in the WHERE clause.

请记住，如果查询vEmployees视图，则由于WHERE子句中的条件，该视图应仅返回销售代表记录。

So far, in learning the CREATE VIEW SQL statement, we also mentioned a few cool and useful tricks, and if you ask me, that’s just some really useful things to know about view.

到目前为止，在学习CREATE VIEW SQL语句时，我们还提到了一些很酷且有用的技巧，如果您问我，那只是一些关于视图的真正有用的知识。

删除视图 (Deleting views)

If you ask me, deleting is the simplest thing you can do in SQL Server. Now that I’ve mentioned dropping and re-creating views using the CREATE VIEW SQL statement a few times, let’s just quickly go over it and see the syntax.

如果您问我，删除是您在SQL Server中最简单的操作。现在，我已经提到了使用CREATE VIEW SQL语句删除和重新创建视图几次，让我们快速浏览一下它并查看语法。

All we need to do is write the DROP VIEW statement that is used for deleting one or more views from the current database. When you need to drop multiple views, just specify all names of the views to remove separated by a comma. In our case, we got two:

我们需要做的就是编写DROP VIEW语句，该语句用于从当前数据库中删除一个或多个视图。当需要删除多个视图时，只需指定要删除的所有视图名称（用逗号隔开）即可。在我们的例子中，我们得到两个：

USE SQLShackDB;
GO
 
--Remove views from the current database
DROP VIEW vEmployees, vEmployeesWithSales;
GO

Once executed, the definition and other information about the views are deleted from the system catalog:

执行后，将从系统目录中删除有关视图的定义和其他信息：

结论 (Conclusion)

In this third part of learning the CREATE VIEW SQL statement, we saw how we can do data modifications through views. In doing so, we also learned a few cool and useful tricks that we can do with views using the WITH CHECK OPTION. We also saw how to rename and delete views from the current database. In the next article, we will take a look at indexed views and how to work with them. We will learn the SCHEMABINDING used to bind the view to the schema of the underlying table.

在学习CREATE VIEW SQL语句的第三部分中，我们了解了如何通过视图进行数据修改。通过这样做，我们还学习了一些可以使用WITH CHECK OPTION对视图进行操作的酷而有用的技巧。我们还看到了如何重命名和删除当前数据库中的视图。在下一篇文章中，我们将看一下索引视图以及如何使用它们。我们将学习用于将视图绑定到基础表的架构的SCHEMABINDING。

I hope this article has been informative for you and I thank you for reading it. Stay tuned for the next one…

希望本文对您有所帮助，也谢谢您阅读。请继续关注下一个…

目录 (Table of contents)

CREATE VIEW SQL: Creating views in SQL Server

CREATE VIEW SQL: Modifying views in SQL Server

CREATE VIEW SQL: Inserting data through views in SQL Server

CREATE VIEW SQL: Working with indexed views in SQL Server

创建视图SQL：在SQL Server中创建视图

创建视图SQL：在SQL Server中修改视图

CREATE VIEW SQL：通过SQL Server中的视图插入数据

CREATE VIEW SQL：在SQL Server中使用索引视图

翻译自: https://www.sqlshack/create-view-sql-inserting-data-through-views-in-sql-server/

更多推荐

CREATE VIEW SQL：通过SQL Server中的视图插入数据

CREATE VIEW SQL：通过SQL Server中的视图插入数据

介绍 (Introduction)

重命名视图 (Renaming views)

使用第三方软件解决方案重命名视图 (Renaming views using a third-party software solution)

通过视图修改数据 (Data modifications through views)

在视图中使用WITH CHECK OPTION (Using WITH CHECK OPTION in views)

删除视图 (Deleting views)

结论 (Conclusion)

目录 (Table of contents)

发布评论取消回复

最近发表

热门文章

标签列表

CREATE VIEW SQL：通过SQL Server中的视图插入数据

介绍 (Introduction)

重命名视图 (Renaming views)

使用第三方软件解决方案重命名视图 (Renaming views using a third-party software solution)

通过视图修改数据 (Data modifications through views)

在视图中使用WITH CHECK OPTION (Using WITH CHECK OPTION in views)

删除视图 (Deleting views)

结论 (Conclusion)

目录 (Table of contents)

相关文章

发布评论取消回复

最近发表

热门文章

标签列表