Procedure oracle to copy data between tables

Question

Navigation

#1 by (5 votes)

4

I need to do a procedure that copies from my table TAB_FORNECEDOR, everything in it and go to table TAB_FORNECEDOR2.

I need to make a cursor with a loop, a delete with commit first of all, can you give me a light on how to mount my procedure?

oracle procedure stored-procedures select

asked by anonymous 16.12.2014 / 18:52

1 answer

Decode HTML entities in a string in Python How to create button in Android's behind code?

score 5 · Answer 1

One solution is to create the table from scratch with data from the previous one:

CREATE TABLE
   TAB_FORNECEDOR2
AS
   SELECT
      *
   FROM
      TAB_FORNECEDOR;

Remembering that you should remove TAB_FORNECEDOR2 if it already exists.

If you just need to keep both synced, better give more detail on the question.

Important : If you need to define constraints on the copy, you have to do this manually, as CREATE AS does not copy indexes, triggers, etc. >

If you want to copy only the data, you can use this syntax:

INSERT INTO
   TAB_FORNECEDOR2 (
      campo1,
      campo2
      ...
   )
SELECT
   TAB_FORNECEDOR.campo1 ...
FROM
   TAB_FORNECEDOR

And optionally you can use WHERE to filter the desired rows.