Forget about the question first,
I think i should made much more clear about my question by providing some example of my query inside my stored prod
assume I have a destination table TBLA and a source table TBLB.
Step 1: I get the sql from a table of my database for example
--I Ran this process in a cursor that @Sql is actually a pass in selected coloum 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 my problem describe 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 problem more detail in Step 2:
Basically, After i performe the delete statment, i found that all of my data is missing and seem its just perform first row only that's DELETE FROM TBLA...
For example senario
Before I try to made the delete statement, I try just to select out ##TempTable value, it's correct. Oh yeah, it's only have 2011-11-04 data
IN fact my TBLA currently already have 2011-11-03 and 2011-11-02 data.
PS: I actually selecting data by a date value.
This I ran the above sql,its success...
And continues than, I open my TBLA and see the result. OMG!!!! its only have 2011-11-04 data... where is the rest??
PLEASE NOTE THAT:
This above sql is just a part of my dyamic stored prod that used sp_executesql..
My actual data is i have 300 of table need to be process and properly total 300 table have different unique indentityso i cant made the Select * From ##Temptable Where id=something
Hope this much clearer the question
Thanks you, Appreciate your help
REgards:
LiangCk
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..
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.