Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

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.

Tags:
Former Member
replied

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

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question