0

I have 2 tables given with variables:

SOURCE_TABLE_NAME STRING(10) := 'TABLE1';
TARGET_TABLE_NAME STRING(10) := 'TABLE2';

I need to write a stored procedure at Oracle 12 PL/SQL to copy some rows from SOURCE_TABLE_NAME to TARGET_TABLE_NAME.

Which way is it better to use?

  • CURSOR
  • SYS_REFCURSOR
  • Any other ways?
VictorDDT
  • 583
  • 9
  • 26
  • I mean to copy some rows between 2 existing TABLES in the database – VictorDDT Mar 17 '20 at 02:03
  • Do you mean something like this? https://www.db-fiddle.com/f/hnRNqtMdST8QKBG7ihSe2p/0 . You can use `INSERT INTO SELECT`: https://stackoverflow.com/a/13237661/3654837, https://stackoverflow.com/a/7483174/3654837 – Mukyuu Mar 17 '20 at 02:04
  • Thank you, good idea. May I be sure that columns and values are related properly between 2 tables? – VictorDDT Mar 17 '20 at 02:11
  • `INSERT INTO SELECT` copies data from one table to another table **AND** requires that data types in source and target tables match. You can try using `CAST` if the data types differ. – Mukyuu Mar 17 '20 at 02:14
  • It does match. 2 tables have absolutely the same columns (don't ask me why I'm not using VIEWs). `INSERT INTO` works when I define columns like: `INSERT INTO table2(col1) SELECT col1 FROM table1`. But in my case I don't have list of columns in advance. – VictorDDT Mar 17 '20 at 02:19
  • I guess it doesn't work in my case because of different columns order in the tables. I'll check it. – VictorDDT Mar 17 '20 at 02:26

1 Answers1

0

If you don't have any list of columns in advance but have absolutely same columns, you could use:

INSERT INTO table_name_to_go SELECT * FROM initial_table_name;

If they have different columns order you need to redefine them in your SELECT example:

INSERT INTO table_name_to_go -- column order: C1,C3,C2 and initial table order: C1,C2,C3
SELECT C1, C3, C2 FROM initial_table_name;

Db Fiddle

Mukyuu
  • 6,436
  • 8
  • 40
  • 59