主页 / dba / 问题 / 202673
Accepted
Learning_Learning
Asked: 2018-03-31 00:55:26 +0800 CST

尝试从查询将值插入临时表时出现问题

  • 772

请在下面帮助我。

我正在尝试使用 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
sql-server t-sql

2 个回答

  1. Best Answer

    忽略数据库名称“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;
    • 4

  2. 你也可以直接插入#表，不是吗？

    SELECT *
INTO #TempTable
FROM dbo.table1

    然后您不必担心首先创建able（如果您修改后续的 SELECT 语句，则必须修改其定义）

    这是您的代码的重写

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

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]
into #BackupStatus
FROM    [MostRecentBackupStatus_CTE] c
        INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];

        SELECT * FROM #BackupStatus
        DROP Table #BackupStatus

    在 SQL Server 2016 中，您还可以使用 DROP TABLE IF EXISTS #BackupStatus https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server -2016/

    我希望这有帮助，

    乙

    • 1

相关问题