Delete temporary table from SQL database using Stored procedure

Category: azure data factory

Question

zzzsharepoint on Tue, 24 Dec 2019 13:49:12


Hi, 
Its rather a very simple question but I am not able to make it work. 
I am inserting data from a temporary table in SQL DB to a Main table in DB using ADF.
I have two pipeline:

1- Copy activity from a blob file to Azure Sql temporary table.
2- Incremental upload of data from Azure Sql temporary table to Main table with same schema and also updating the watermark value.


Everything is working fine as I followed this article https://docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-portal
But I also want to remove the data from my temporary table as it doesnt make sense to keep the data there and for that I inserted a simple line at the end of my stored procedure "DELETE FROM temp_table" but I dont see the records to be deleted from the temp table.
Not sure what wrong I am doing. Here is the full stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Create Stored Procedure */
ALTER PROCEDURE [dbo].[usp_UpdateWatermark] 
 @tableName nvarchar(500)
 
AS 
BEGIN

 DECLARE
 /* ============= Variables ============= */
  @watermarkValue  nvarchar(MAX) 
 
 /* Determine latest Watermark value from input table */
 SELECT
  @watermarkValue = MAX([LOAD_DATE]) 
 FROM dbo.Fish_Transfer AS T
 
 /* Update Watermark table */
 UPDATE dbo.WatermarkTable
 SET  WatermarkValue  = @watermarkValue 
 WHERE TableName = @tableName 
 DELETE FROM dbo.Fish_Transfer
END


Thanks in advance


zzzSharePoint

Replies

Vaibhav.Chaudhari on Tue, 24 Dec 2019 14:13:40


Is dbo.Fish_Transfer is the temporary table that you want to delete? The DELETE FROM statement looks correct and should delete the data. 

What happens when you run the SP alone in SSMS? Does it work and delete the data? 

There is another option which you can use to delete data i.e. in Copy activity Sink - use PreCopy option and use truncate table statement. 

Please check step 9 in below:

http://microsoft-bitools.blogspot.com/2019/06/staging-with-azure-data-factory-foreach.html

zzzsharepoint on Tue, 24 Dec 2019 14:30:16


Hi @Vaibhav

The delete statement works when you run it alone in SSMS and the above example in Pre script wont work for my case as per the error @item() syntax can only be used in the filter activity or child activities of a foreach activity

And I am not using any For each activity for now in my pipeline.

What next??

Thanks

zzzsharepoint on Tue, 24 Dec 2019 15:02:44


Also I am using copy activity and after that I calling the Stored procedure activity which is actually updating the watermark value in watermark table.

So have to call the delete or truncate statement after I call the copy activity. so have to do it in Stored procedure

Vaibhav.Chaudhari on Wed, 25 Dec 2019 18:17:55


The delete statement works when you run it alone in SSMS and the above example in Pre script wont work for my case as per the error @item() syntax can only be used in the filter activity or child activities of a foreach activity

Maybe the SP is not getting executed at all. No clue here.

On pre-copy part - When data is loaded to temporary table, use pre-copy to truncate the table using "TRUNCATE TABLE dbo.Fish_transfer_temporary" and then it will be loaded with new data.

Later, incremental data can be loaded from dbo.Fish_transfer_temporary to dbo.Fish_Transfer_Main.

In the procedure, you can get max(Load_date) from dbo.Fish_Transfer_Main and stamp it in watermark table.

dataflowuser on Mon, 30 Dec 2019 20:05:21


There are much easier pattern using dataflows and update/lookup transformations to do this in a code free manner without handling temporary tables.

HimanshuSinha-msft on Mon, 30 Dec 2019 21:46:52


I did tested the SP activity and it does delete records from the table .  I suggest you to please do the following 

1 . Open SSMS 

2. run the command 

exec [dbo].[usp_UpdateWatermark]  'dbo.Fish_Transfer'

If this works fine , then can you sure that the SP is doing what it should . 

On a other note the proc has an parameter @tableName

ALTER PROCEDURE [dbo].[usp_UpdateWatermark] @tableName nvarchar(500)

I could have a updated the proc a bit to make it more generic . 

 DELETE FROM @tableName 


I am adding my queries below , if that helps you troubleshoot and saves some of your time .

CREATE PROCEDURE [DBO].[DELETE_RECORD_TEST] @TABLENAME VARCHAR(100) AS

BEGIN DELETE FROM [DBO].[TBL_DELETE_RECORD_TEST] END;

CREATE TABLE [TBL_DELETE_RECORD_TEST](TABLENAME VARCHAR(100)  )

SELECT * FROM [TBL_DELETE_RECORD_TEST] 

INSERT INTO [TBL_DELETE_RECORD_TEST] VALUES ('DBO.FISH_TRANSFER')

EXEC [DBO].[DELETE_RECORD_TEST]  'DBO.FISH_TRANSFER'