SQL Server: Delete by selecting value from temp table

Question

I have a destination table TBLA and a source table TBLB.

STEP 1: I get the SQL from a table of my database:

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

STEP 2: I delete the destination table data by calling another sp_ExecuteSql:

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.

STEP 3: Insert into TBLA by select * from temp table

DECLARE @DumpSql NVARCHAR(MAX);
SET @DumpSql = 'INSERT INTO TBLA 
                SELECT * FROM ##TempTable';

EXEC sp_ExecuteSql @DumpSql ;

As mentioned at the end of STEP 2, here is the issue:

After performing the delete statement I found that all my data is missing and it seems it just performed the first command only (DELETE FROM TBLA).

Before I tried the delete statement, I commented out the ##TempTable value, and it worked and I only had 2011-11-04 data. (TBLA already had 2011-11-03 and 2011-11-02 data).

I am actually selecting data by a date value. So far it seems to be successful.

But then, I open my TBLA and see the result only contains 2011-11-04 data...where is the rest?

PLEASE NOTE THAT:

This above SQL is just a part of my dynamic stored prod that used sp_executesql... 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 : SELECT * FROM ##Temptable WHERE id=bla

What would be the best way to go about this?

score 3 · Answer 1 · edited May 23 '17 at 11:50

3

check this: Delete rows from mytable using a subquery in the where condition:

DELETE FROM mytable WHERE id IN (SELECT id FROM mytable2)

If you are looking for unique values to delete according to date then create unique values in your Temporary table and then perform delete operation as you are doing.

Secondly if you are looking for delete operation somewhat based on join operation: then follow this link:
How do I delete from multiple tables using INNER JOIN in SQL server

Check these links to know how to insert row in temp table using SP_Execute :
Sql server - how to insert single row into temporary table?
Insert multiple rows into temp table with one command in SQL2005
hope these help you little..

edited May 23 '17 at 11:50

Community

1
1

answered Dec 01 '11 at 08:10

Niranjan Singh

18,017
2
42
75

Thanks for reply, But unfortuanlly it cannnot identified by an id because its is dynamically way that applied. and problem have different type of table. I ran this sql inside a stored prod – Worgon Dec 01 '11 at 08:12
Please help to see my question again..hopefully its much more clear now – Worgon Dec 01 '11 at 09:18
i have updated some links that will help you to proceed further to solve this problem.. – Niranjan Singh Dec 01 '11 at 10:30

dani herrera · Answer 2 · 2011-12-01T09:44:17.457

First of all, you should identify primary key fields for TBLA. I assume that PK is FLDA.

When you delete from TBLA rows that exists in temp table, the right sintax is:

DELETE FROM TBLA
WHERE FLDA IN 
   (SELECT ##TempTable.FLDA 
   FROM ##TempTable)

A more elegant way to solve this with sql server is MERGE statement:

MERGE 
    INTO  TBLA  AS target
    USING (Select * from ##TempTable)
    ON TBLA.FLDA = ##TempTable.FLDA
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]

Edited I have seen your more detailed question.

When you execute:

DELETE FROM TBLA WHERE EXISTS(SELECT * FROM ##TempTable)

all your data will be delete because where condition allways return True.

A work around may be concatenate all table fields from TBLA to compare with concatenate all table fields from ##TempTable.

Please help to see my question again..hopefully its much more clear now — Worgon, Dec 01 '11 at 09:17

SQL Server: Delete by selecting value from temp table

2 Answers2