cancel
Showing results for 
Search instead for 
Did you mean: 

Execute Stored Procedure that uses "ALTER TABLE" and "DELETE FROM"

Former Member
0 Kudos

Business requirement: append the monthly new data into an archive table, then delete the oldest month data in the archive table. The archive table only keeps the most recent two months of data.


Example: we have February and March data in ARCHIVE. When April data comes, we append April data to the table, then delete February.


Our solution: create a stored procedure, steps below:


  1. Insert data into Archive table (sql: INSERT INTO)
  2. Add a column called “FLAG” (ALTER TABLE)
  3. Assign value to “FLAG”, assign 1 to the most recently two months, assign 0 to others (UPDATE)
  4. Delete records that have “FLAG” = 0 (DELETE FROM)
  5. Delete column “FLAG”(ALTER TABLE)
  6. Repeat every month

Question:

Since my procedure involves ALTER TABLE and DELETE FROM, I can’t execute them all at once because they depend on others. What’s the best way to run all these steps in sequence? Do I need 4 different procedures, call one by one, or is there a better way to execute all these procedures?


Note: I tried to use dynamic EXEC but I wasn't allow to exec ALTER TABLE using EXEC.

Accepted Solutions (1)

Accepted Solutions (1)

nithinu
Participant
0 Kudos

Hello Meichun,


I just played around with your requirement and created some sample tables and procedure. I assume that your source table is having a date filed. Can you have a look on my below experiment,


CREATE TABLE NIT_SCN.SOURCE_TABLE( DT DATE, VAL1 INTEGER);

-- Inserted three months data in above table

CREATE TABLE NIT_SCN.ARCHIVE_TABLE( DT DATE, VAL1 INTEGER);

CREATE PROCEDURE NIT_SCN.MANAGE_ARCHIVE_SP(OUT min_dt DATE)

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA NIT_SCN

AS

BEGIN

  INSERT INTO NIT_SCN.ARCHIVE_TABLE

  SELECT S.DT,S.VAL1

  FROM NIT_SCN.SOURCE_TABLE S

  WHERE S.DT > IFNULL((SELECT MAX(DT) FROM NIT_SCN.ARCHIVE_TABLE),ADD_DAYS(S.DT,-1) );

  DELETE FROM NIT_SCN.ARCHIVE_TABLE

  WHERE DT <=

  (

  SELECT ADD_MONTHS(LAST_DAY(MAX(DT)),-2)

  FROM NIT_SCN.ARCHIVE_TABLE

  );

  SELECT MIN(DT) INTO min_dt

  FROM NIT_SCN.ARCHIVE_TABLE;

END;

CALL NIT_SCN.MANAGE_ARCHIVE_SP(?);

Regards,

Nithin

Former Member
0 Kudos

Slightly modified your codes, and now it works perfectly

Answers (2)

Answers (2)

Former Member
0 Kudos


Meichun,

In your step 3, you are able to identify records under category FLAG = 0. Use this logic to delete those records instead.

You, actually, do not need an additional column to handle this requirement.

Former Member
0 Kudos

Hi Tarun,

Can you be more specific?

The reason we are using FLAG is because after a data load, the ARCHIVE table might contain data from different months, we just need the most recently two months in the ARCHIVE, regardless of if they are not the current month(i.e. March'16) and the previous months(i.e. Feb'16).

Thank you.

Meichun

pfefferf
Active Contributor
0 Kudos

Hello Meichun,

the question is why you need the additional "FLAG" column. To be able to identify for which record sets the "FLAG" column needs to be set to 0 to indicate the deletion you must be able to identify the records to be deleted without the "FLAG" column. Why not just using that "identifier logic" for the deletion instead of going the unnecessary steps to introduce a "FLAG" column, set the column to a value, remove the values and remove the "FLAG" column?

Regards,

Florian

Former Member
0 Kudos

Hi Florian,

If I understand correctly, the reason is that the supposedly monthly load might or might not happen every month, so we can't really use something like system date then only keep this month and last month. It will have to be based on the records available in the Archive table, then we only keep the most recently two month data within the Archive table.

Makes sense?

Thank you

Meichun

pfefferf
Active Contributor
0 Kudos

But you have a logic how you determine if the "FLAG" column should be set to 1 or 0. Why this logic is not only applied for the deletion? Where is the overlap or the conflict with the new data?

Former Member
0 Kudos

I use RANK_DENSE() to help me find the top two months.

pfefferf
Active Contributor
0 Kudos

That window function is not really an answer to the question. The question was why it is not possible to apply the same logic as you use to determine the value for the intermediate "FLAG" column instead of applying the logic directly to the deletion?

Former Member
0 Kudos

I see, let me try it