在 SELECT INTO 上锁定数据库 [英] Locking database on SELECT INTO

问题描述

我有一个返回大量行的查询，我使用 SELECT INTO(而不是 INSERT INTO)来避免事务日志出现问题.

I have a query that return a huge number of rows and I am using SELECT INTO (instead of INSERT INTO) to avoid having problems with transaction log.

问题是:在此查询运行时，我可以读取对象，但无法在对象资源管理器中显示它们.例如，当我尝试展开表格项目时，我收到以下消息:

The problem is: while this query is running, I can read objects but not showing them in object explorer. When I try to expand the tables item, for example, I receive the message bellow:

有没有办法避免这个问题?

推荐答案

现在为您准备一个测试，它将回答您的问题...

Now here is a Test for you which will give answer to your question...

在 SSMS 中打开一个查询窗口.编写任何将返回任何数字或行的查询，可能只有一行或可能是 10.并执行以下操作

Open a Query window in SSMS. Write any query which will return any number or rows, could be only one row or maybe 10. and do as follows

BEGIN TRANSACTION;

SELECT *  
   INTO NEW_Test_TABLE
FROM TABLE_NAME

查询窗口 2

现在打开另一个窗口并针对这个 NEW_Test_TABLE 编写一个 SELECT 语句.

Query Window 2

Now Open another window and write a SELECT statement against this NEW_Test_TABLE.

SELECT * FROM NEW_Test_TABLE

您的查询永远不会执行完，不会返回任何结果(此时 NEW_Test_TABLE 仅存在于缓冲区缓存中).除非您返回到第一个查询窗口并提交事务，并且如果您转到查询窗口 1 并且 ROLLBACK TRANSACTION NEW_Test_TABLE 将在缓冲区缓存中存在一次并且不再存在于任何地方.

Your Query will never finish executing,,, no results will be returned (At this time NEW_Test_TABLE only exists in buffer chache). Unless you go back to your 1st Query Window and commit the transaction, And if you goto query window 1 and ROLLBACK TRANSACTION NEW_Test_TABLE would have existed once in buffer chache and no longer exist anywhere.

同样，当您的 Select into 语句在执行时没有任何内容提交到磁盘，因此 SSMS 无法看到它，也无法通过对象资源管理器向您显示有关它的任何信息.

Similarly when your Select into statement in being executed nothing is committed to disk, therefore SSMS cannot see it neither can show you any information about it via Object explorer.

所以答案是在执行查询时耐心等待，让 SQL Server 将 SELECT INTO 事务提交到磁盘，您将能够通过查询它或通过对象资源管理器访问它.

So the answer is while the query is being executed be patient and let SQL Server Commit the SELECT INTO transaction to disk and you will be able to access it VIA querying it or via Object explorer.

这篇关于在 SELECT INTO 上锁定数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！