CODEWITHSUNDEEP
plsqloracle11goracle-sqldeveloper
Axel Ktad
Axel Ktad

Reputation: 31

How to add a date with an INSERT INTO SELECT in PL SQL?

For my homework I need to make a package that contains a procedure that removes all records from the table dwdimartist and then fills the table with all records from the table artist and add a field with the date of today.

This is what I've created, it works but I don't know if it's performant enough. Is it better to use a cursor that loops over each record in the table artist?

CREATE OR REPLACE PACKAGE BODY dwh AS  

  PROCEDURE filldwh IS
    today_date DATE := SYSDATE;
  BEGIN
    DELETE FROM dwdimartist;

    INSERT INTO dwdimartist (artistkey, name) (SELECT artistid, name FROM artist);
    UPDATE dwdimartist SET added = today_date;
  END filldwh;
END dwh;

Upvotes: 0

Views: 828

Answers (2)

J. Chomel
J. Chomel

Reputation: 8395

Simple SQL query like you did is better choice than a cursor or implicit loop.

possible improvement: You should do it at once without update: set the date during insert.

INSERT INTO dwdimartist (artistkey, name, added) 
  (SELECT artistid, name, sysdate FROM artist);

Hope it helps

Upvotes: 5

Slava Senchenko
Slava Senchenko

Reputation: 51

You don’t need to use cursors. You can hardly beat Insert ... select since it’s SQL itself and in most cases it works better than any programmatic structure due to native dbms optimization. However, you can do better if you decrease number of I/O operations. You don’t need update here. Simply add sysdate to your select and insert everything together.

insert into dwdimartist(artistkey, name, added) select artistid, name, sysdate from artist;

Another thing to improve is your ‘delete’. While it’s small table for learning purposes you won’t feel any difference between delete and truncate but for real ETL tasks and big tables you’ll see it definitely. Yes you cannot use truncate directly in your PL/SQL but there are 3 ways to do it:

  1. execute immediate ‘truncate dwdimartist’;
  2. Dbms_utility.exec_ddl_statement(‘truncate ...;’);
  3. DBMS_SQL package

However, remember that calling truncate will execute commit like any ddl command. So delete may be the only option sometimes. But even if it is, you should avoid it for the whole table.

Upvotes: 1

Related Questions