title: SQL Server: Delete by selecting value from temp table
body: <p>I have a destination table <code>TBLA</code> and a source table <code>TBLB</code>.</p>
  <p>STEP 1: I get the SQL from a table of my database:</p>
  <pre><code>-- I ran this process in a cursor such that `@Sql` is a pass-in selected column value:
  DECLARE @Sql NVARCHAR(MAX);
  SET @Sql = 'SELECT TBLB.coloumA, TBLB.coloumB... into ##TempTable
              FROM TBLB 
              WHERE CONVERT(VARCHAR(10),Date,120)='2011-04-05'';
  
  EXEC sp_ExecuteSql @sql
  </code></pre>
  <p>STEP 2: I delete the destination table data by calling another <code>sp_ExecuteSql</code>:</p>
  <pre><code>DECLARE @CheckClear NVARCHAR(MAX);
  SET @CheckClear = 'DELETE FROM TBLA WHERE EXISTS(SELECT * FROM ##TempTable)';
  
  EXEC sp_ExecuteSql @CheckClear ;
  -- This section is where the problem starts, described below.
  </code></pre>
  <p>STEP 3: Insert into TBLA by select * from temp table</p>
  <pre><code>DECLARE @DumpSql NVARCHAR(MAX);
  SET @DumpSql = 'INSERT INTO TBLA 
                  SELECT * FROM ##TempTable';
  
  EXEC sp_ExecuteSql @DumpSql ;
  </code></pre>
  <p>As mentioned at the end of STEP 2, here is the issue:</p>
  <p>After performing the delete statement I found that all my data is missing and it seems it just performed the first command only (<code>DELETE FROM TBLA</code>).</p>
  <p>Before I tried the delete statement, I commented out the <code>##TempTable</code> value, and it worked and I only had 2011-11-04 data. (<code>TBLA</code> already had 2011-11-03 and 2011-11-02 data).</p>
  <p>I am actually selecting data by a date value. So far it seems to be successful.</p>
  <p>But then, I open my <code>TBLA</code> and see the result only contains <code>2011-11-04</code> data...where is the rest?</p>
  <p>PLEASE NOTE THAT:</p>
  <p>This above SQL is just a part of my dynamic stored prod that used <code>sp_executesql</code>...
  My actual data consists of about 300 tables that I need to process. Each of these tables have different unique identities so I can't use : <code>SELECT * FROM ##Temptable WHERE id=bla</code></p>
  <p>What would be the best way to go about this?</p>