时间戳

首页 > 解决方案 > 即使执行了 select * into 命令，存储过程也不会创建临时表

sql-server - 即使执行了 select * into 命令，存储过程也不会创建临时表

问题描述

我最近遇到了一个问题，即我的 SQL Server 存储过程在特别要求时没有创建临时表。但是，它不会引发错误。当我尝试将数据插入临时表时失败。您能否指出我在此过程中做错了什么？

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[CDC_PROC]
@SourceSystemName nvarchar(30),
@DomainTypeDescription nvarchar(30), 
@HashDescription nvarchar(30)
AS 
BEGIN 
  SET NOCOUNT ON;

  DECLARE @CreateInsertTableSql NVARCHAR(MAX);
  DECLARE @CreateUpdateTableSql NVARCHAR(MAX);
  DECLARE @InsertNewIntoTgtTableSql NVARCHAR(MAX);
  DECLARE @InsertUpdatedIntoTgtTableSql NVARCHAR(MAX);
  DECLARE @InsertUpdatedContractsSQL NVARCHAR(MAX);
  DECLARE @InsertNewContractsSQL NVARCHAR(MAX);
  DECLARE @UpdateTimeStampsSQL NVARCHAR(MAX);
  DECLARE @TargetTableName NVARCHAR(128);
  DECLARE @InsertTempTableName NVARCHAR(128);
  DECLARE @UpdateTempTableName NVARCHAR(128);
  DECLARE @TempTableColumnList NVARCHAR(MAX);
  DECLARE @TargetTableColumnList NVARCHAR(MAX);

-- Setting target and temporary table names as needed.
 SET @TargetTableName =  @DomainTypeDescription + N'AAH'
 SET @InsertTempTableName = N'#' + @DomainTypeDescription + N'AAH_InsertTemp'
 SET @UpdateTempTableName = N'#' + @DomainTypeDescription + N'AAH_UpdateTemp'

-- Creating temp tables.
 SET @CreateInsertTableSql = N'select * into ' + @InsertTempTableName + N' from (select * from dbo.' + @DomainTypeDescription + N' where 1=2) AS X'
 SET @CreateUpdateTableSql = N'select * into ' + @UpdateTempTableName + N' from (select * from dbo.' + @DomainTypeDescription + N' where 1=2) AS Y'

 print @CreateInsertTableSql
 print @CreateUpdateTableSql

 EXECUTE sp_executesql @CreateInsertTableSql
 EXECUTE sp_executesql @CreateUpdateTableSql

-- Capturing field names from target and temp tables for processing.
 SET @TempTableColumnList = N'(' + (SELECT STRING_AGG(COLUMN_NAME, ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @DomainTypeDescription and COLUMN_NAME not like '%PointOfView%Date') + N')'
 SET @TargetTableColumnList = (SELECT STRING_AGG(COLUMN_NAME, ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TargetTableName and COLUMN_NAME not like '%PointOfView%Date')

-- Executing procedures to identify New and Updated contracts. Capturing results in the temporary tables.
 SET @InsertUpdatedContractsSQL = N'insert ' + @UpdateTempTableName
         + @TempTableColumnList +
' Exec [spFindUpdatedDomainRecords] @SourceSystemName = ' + @SourceSystemName + ', @DomainTypeDescription = ' + @DomainTypeDescription + ', @HashDescription = ' + @HashDescription

 SET @InsertNewContractsSQL = N'insert ' + @InsertTempTableName
         + @TempTableColumnList +
 ' Exec [spFindNewDomainRecords] @SourceSystemName = ' + @SourceSystemName + ', @DomainTypeDescription = ' + @DomainTypeDescription 

-- Inserting data from temporary tables into target table.
 SET @InsertNewIntoTgtTableSql = N'insert into ' + @TargetTableName + N' (' + @TargetTableColumnList + N', PointOfViewStartDate, PointOfViewStopDate) select ' + @TargetTableColumnList + N', NULL, NULL from ' + @InsertTempTableName
 SET @InsertUpdatedIntoTgtTableSql = N'insert into ' + @TargetTableName + N' (' + @TargetTableColumnList + N', PointOfViewStartDate, PointOfViewStopDate) select ' + @TargetTableColumnList + N', NULL, NULL from ' + @UpdateTempTableName

-- Updating Point of View Start and Stop dates with current date and high end dates respectively.
 SET @UpdateTimeStampsSQL = N'update ' + @TargetTableName + N' set  PointOfViewStartDate = cast(GETDATE() as datetime2(0)), PointOfViewStopDate = cast(''9999-12-31 00:00:00'' as datetime2(0))'

 EXECUTE sp_executesql @InsertUpdatedContractsSQL
 EXECUTE sp_executesql @InsertNewContractsSQL
 SET IDENTITY_INSERT @TargetTableName ON
 EXECUTE sp_executesql @InsertNewIntoTgtTableSql
 EXECUTE sp_executesql @InsertUpdatedIntoTgtTableSql
 SET IDENTITY_INSERT @TargetTableName OFF
 EXECUTE sp_executesql @UpdateTimeStampsSQL

END

我用下面的命令执行它。它给出了一个错误。

Exec AAH_CDC_PROC @SourceSystemName = 'XYZ', @DomainTypeDescription = 'ABC', @HashDescription = 'AAH';

Msg 208, Level 16, State 0, Line 1
Invalid object name '#ABCAAH_UpdateTemp'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#ABCAAH_InsertTemp'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#ABCAAH_InsertTemp'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#ABCAAH_UpdateTemp'.

标签： sql-serverazure-database-mysql

解决方案

你需要让它##Table不是一个#Table

而且您可能还必须##Table在创建之前先检查是否存在。

推荐阅读