Stored Procedure doesn't create a temp table even after the select * into command is executed

Question

I recently ran into an issue where my SQL Server stored procedure doesn't create a temporary table when specifically asked to do so. However, it doesn't throw an error. It fails when I try to insert data into the temp table. Can you please point towards what am I doing wrong in this procedure?

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

I execute it with below command. It gives out an error.

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'.

score -1 · Answer 1 · edited Jan 21 '20 at 00:13

-1

You need to make it a ##Table not a #Table

And you'll possibly also have to check IF the ##Table exists first before creating.

edited Jan 21 '20 at 00:13

Dale K

25,246
15
42
71

answered Jan 20 '20 at 21:16

Rodney Ellis

727
2
13
28

Could you explain why please – Dale K Jan 20 '20 at 21:25
https://stackoverflow.com/questions/8040105/execute-sp-executesql-for-select-into-table-but-cant-select-out-temp-table-d – Rodney Ellis Jan 20 '20 at 21:35
https://stackoverflow.com/a/53198806/864696 – Ross Presser Jan 20 '20 at 21:36
2

I'm asking you to make your answer a complete answer by explaining why your suggestion is the answer. All answers on SO should be stand alone and make sense to anyone coming to read them at any time now or in the future. – Dale K Jan 20 '20 at 21:41
And if the answer already exists you should be flagging the question as a duplicate, not answering it. – Dale K Jan 20 '20 at 21:42
4

I get you want to help the OP, thats exactly why I asked you to explain why your question is the answer, because otherwise the OP doesn't understand how it fixes their problem and will likely ask a similar question again in future. This is your chance to assist with their learning. The purpose of SO is to create a repository of information which will be valuable for programmers now and in the future, hence why answers should be as detailed and stand-alone as possible so they continue to benefit the community for years to come. – Dale K Jan 20 '20 at 21:49

Stored Procedure doesn't create a temp table even after the select * into command is executed

1 Answers1