Sql-Server

嘗試從查詢將值插入臨時表時出現問題

  • March 30, 2018

請在下面幫助我。

我正在嘗試使用 sql 查詢將值插入到臨時表中。但我面臨以下錯誤。

請幫助我理解錯誤。

我面臨的錯誤：

忽略數據庫名稱“tempdb”，引用 tempdb 中的對象。

忽略數據庫名稱“tempdb”，引用 tempdb 中的對象。

忽略數據庫名稱“tempdb”，引用 tempdb 中的對象。

消息 156，級別 15，狀態 1，第 17 行關鍵字“使用”附近的語法不正確。

消息 3701，級別 11，狀態 5，第 91 行無法刪除表“#BackupStatus”，因為它不存在或您沒有權限。

到目前為止我嘗試過的查詢：

IF OBJECT_ID('tempdb..#BackupStatus') IS NOT NULL 
DROP Table tempdb..#BackupStatus

create table tempdb..#BackupStatus(

Server_name VARCHAR(50),
Full_Backup_Status_Weekly VARCHAR(50),
Diff_Backup_Status_Daily VARCHAR(50),
Transaction_log_backup_Hourly VARCHAR(50))

--select * from #BackupStatus

insert into tempdb..#BackupStatus
(Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly)
--select Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly from
values
use msdb
go

WITH [MostRecentBackupStatus_CTE]
AS
(
   SELECT  bsfull.[server_name] ,
           bsfull.[database_name] ,
           bsfull.[backup_finish_date] AS [last_full_backup] ,
           bsdiff.[backup_finish_date] AS [last_diff_backup] ,
           bstlog.[backup_finish_date] AS [last_tran_backup] ,
           DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
           DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
           DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] 

   FROM    [msdb]..[backupset] AS bsfull
           LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
                                                      AND bstlog.[server_name] = bsfull.[server_name]
                                                      AND bstlog.[type] = 'L'
                                                      AND bstlog.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                           FROM    [msdb]..[backupset] b2
                                                                                           WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                   AND b2.[server_name] = bsfull.[server_name]
                                                                                                   AND b2.[type] = 'L') )
           LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
                                                      AND bsdiff.[server_name] = bsfull.[server_name]
                                                      AND bsdiff.[type] = 'I'
                                                      AND bsdiff.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                           FROM    [msdb]..[backupset] b2
                                                                                           WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                   AND b2.[server_name] = bsfull.[server_name]
                                                                                                   AND b2.[type] = N'I') )
   WHERE   bsfull.[type] = N'D'
           AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
                                                FROM   [msdb]..[backupset] b2
                                                WHERE  b2.[database_name] = bsfull.[database_name]
                                                       AND b2.[server_name] = bsfull.[server_name]
                                                       AND b2.[type] = N'D') )
           AND EXISTS ( SELECT [name]
                        FROM   [master].[sys].[databases]
                        WHERE  [name] = bsfull.[database_name] )
           AND bsfull.[database_name] NOT IN  (N'tempdb' , N'Master' ,N'Model' , N'MSDB')
)
SELECT  c.[server_name]  ,

      -- c.[database_name] ,
      -- d.[recovery_model_desc] ,
       --c.[last_full_backup] ,
       --c.[last_diff_backup] ,
       --c.[last_tran_backup] ,
       CASE 
           WHEN c.[days_since_full_backup] <= 7 THEN 'Success'
           WHEN c.[days_since_full_backup] >= 7 THEN 'Failed'
       ELSE NULL END AS Full_Backup_Status_Weekly,
           CASE 
           WHEN c.[days_since_diff_backup] <= 1 THEN 'Success'
           WHEN c.[days_since_diff_backup] >= 1 THEN 'Failed'
       ELSE NULL END AS Diff_Backup_Status_Daily,
       CASE 
           WHEN c.[hours_since_tranlog_backup] <= 1 THEN 'Success'
           WHEN c.[hours_since_tranlog_backup] >= 1 THEN 'Failed'
       ELSE NULL END AS Transaction_log_backup_Hourly


      --c.[days_since_full_backup] ,
      -- c.[days_since_diff_backup] ,
      -- c.[hours_since_tranlog_backup] 
       --c.[full_backup_location] ,
       --c.[diff_backup_location] ,
       --c.[tlog_backup_location]
FROM    [MostRecentBackupStatus_CTE] c
       INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];


       DROP Table tempdb..#BackupStatus

忽略數據庫名稱“tempdb”，引用 tempdb 中的對象。

這不是錯誤，它只是資訊消息。這是由您的程式碼引起的insert into tempdb..#BackupStatus。

您應該只使用insert into #BackupStatus（不指定tempdb），因為#已經指向tempdb.

錯誤

關鍵字“use”附近的語法不正確

是由

insert into tempdb..#BackupStatus
(Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly)
--select Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly from
values
use msdb
go

values您的子句中沒有指定值，insert而是有use.

只需指定您的值。

更新

我查看了您的程式碼並了解您不想插入values但您想使用 CTE 從 msdb 插入 select 的結果，所以我重寫了您的程式碼，它應該如下所示：

use msdb
go


IF OBJECT_ID('tempdb..#BackupStatus') IS NOT NULL 
DROP Table #BackupStatus

create table #BackupStatus(

Server_name VARCHAR(50),
Full_Backup_Status_Weekly VARCHAR(50),
Diff_Backup_Status_Daily VARCHAR(50),
Transaction_log_backup_Hourly VARCHAR(50));

--select * from #BackupStatus

WITH [MostRecentBackupStatus_CTE]
AS
(
   SELECT  bsfull.[server_name] ,
           bsfull.[database_name] ,
           bsfull.[backup_finish_date] AS [last_full_backup] ,
           bsdiff.[backup_finish_date] AS [last_diff_backup] ,
           bstlog.[backup_finish_date] AS [last_tran_backup] ,
           DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
           DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
           DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] 

   FROM    [msdb]..[backupset] AS bsfull
           LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
                                                      AND bstlog.[server_name] = bsfull.[server_name]
                                                      AND bstlog.[type] = 'L'
                                                      AND bstlog.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                           FROM    [msdb]..[backupset] b2
                                                                                           WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                   AND b2.[server_name] = bsfull.[server_name]
                                                                                                   AND b2.[type] = 'L') )
           LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
                                                      AND bsdiff.[server_name] = bsfull.[server_name]
                                                      AND bsdiff.[type] = 'I'
                                                      AND bsdiff.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                           FROM    [msdb]..[backupset] b2
                                                                                           WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                   AND b2.[server_name] = bsfull.[server_name]
                                                                                                   AND b2.[type] = N'I') )
   WHERE   bsfull.[type] = N'D'
           AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
                                                FROM   [msdb]..[backupset] b2
                                                WHERE  b2.[database_name] = bsfull.[database_name]
                                                       AND b2.[server_name] = bsfull.[server_name]
                                                       AND b2.[type] = N'D') )
           AND EXISTS ( SELECT [name]
                        FROM   [master].[sys].[databases]
                        WHERE  [name] = bsfull.[database_name] )
           AND bsfull.[database_name] NOT IN  (N'tempdb' , N'Master' ,N'Model' , N'MSDB')
)


insert into #BackupStatus
(Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly)
SELECT  c.[server_name]  ,

      -- c.[database_name] ,
      -- d.[recovery_model_desc] ,
       --c.[last_full_backup] ,
       --c.[last_diff_backup] ,
       --c.[last_tran_backup] ,
       CASE 
           WHEN c.[days_since_full_backup] <= 7 THEN 'Success'
           WHEN c.[days_since_full_backup] >= 7 THEN 'Failed'
       ELSE NULL END AS Full_Backup_Status_Weekly,
           CASE 
           WHEN c.[days_since_diff_backup] <= 1 THEN 'Success'
           WHEN c.[days_since_diff_backup] >= 1 THEN 'Failed'
       ELSE NULL END AS Diff_Backup_Status_Daily,
       CASE 
           WHEN c.[hours_since_tranlog_backup] <= 1 THEN 'Success'
           WHEN c.[hours_since_tranlog_backup] >= 1 THEN 'Failed'
       ELSE NULL END AS Transaction_log_backup_Hourly


      --c.[days_since_full_backup] ,
      -- c.[days_since_diff_backup] ,
      -- c.[hours_since_tranlog_backup] 
       --c.[full_backup_location] ,
       --c.[diff_backup_location] ,
       --c.[tlog_backup_location]
FROM    [MostRecentBackupStatus_CTE] c
       INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];

select * from #BackupStatus;

DROP Table #BackupStatus;

引用自：https://dba.stackexchange.com/questions/202673

相關問答

Sql-Server

如何獲得與一年多相關的文件大小總和的綜合 ServiceID

  • August 21, 2018
檢視問答
Sql-Server

有沒有辦法刪除插入的數據，反之亦然？

  • April 12, 2022
檢視問答
Sql-Server

即使使用非聚集索引和全文索引，SE LECT 查詢也很慢

  • February 21, 2022
檢視問答
Sql-Server

SQL Server 中的臨時表和表變數有什麼區別？

  • February 2, 2022
檢視問答
Sql-Server

如何使用 TSQL 動態更改數據庫

  • September 22, 2021
檢視問答
Sql-Server

使用 OR 條件連接兩個臨時表時減少臨時表掃描

  • September 22, 2021
檢視問答