Home » SQL & PL/SQL » SQL & PL/SQL » populating data from temp tables
populating data from temp tables [message #212041] Wed, 03 January 2007 10:33 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

I have couple of tables in my schema as transaction tables like
T150,T151,T152,T156 without data. The indexe is associated with each concern tables.

I have couple of tables like T150_TMP,T151_TMP,T152_TMP,T156_TMP in the same schema with data.

I need to transfer or populate data on the basis of above scenario.

i.e. T150_TMP TO T150, T151_TMP TO T151 etc. in the same order

For single table, it can be achieved using single SQL statement like below

insert into T150 SELECT * FROM T150_TMP

But, how can I achieve it for numbers of tables at a time using pl/sql script?

Can you please provide me the PL/SQL script to achieve it ?

Your help would highly be appreciated.

Thanks in advance.
Re: populating data from temp tables [message #212056 is a reply to message #212041] Wed, 03 January 2007 11:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If those tables are the only ones in this schema, you might do that relatively easy - in a cursor loop select all tables that do not have _TMP extension, dynamically create INSERT INTO statement and execute it using EXECUTE IMMEDIATE.

But, if there are other tables there, you'll have to distinguish which ones to use and which not. Will you store their names into another table or something else, it is up to you.

However, if list of tables involved isn't too large and is not a subject to change (at least, not frequently), perhaps you'd better create a simple SQL script which would include all INSERT INTO combinations and later run it at the SQL*Plus prompt.
Re: populating data from temp tables [message #212194 is a reply to message #212056] Thu, 04 January 2007 04:13 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

There are 122 tables. I'll appreciate if you can provide me the PL?SQL script using cursor and dynamics SQL statement as I'm not a good developer.

Thanks in advance.
Re: populating data from temp tables [message #212201 is a reply to message #212194] Thu, 04 January 2007 04:20 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, you'd finish it in a matter of half an hour if you start typing this simple SQL script of 122 lines.

However, as you are interested in a more advanced solution, I believe this is a good way to learn something new. I've already given you some hints:
- cursor FOR loop
- EXECUTE IMMEDIATE

Documentation and examples can be found throughout the Internet (if you don't want to read Oracle documentation at http://tahiti.oracle.com).

So, take your time, read, educate yourself and try it - it isn't that difficult. After you finish the script, do come back and ask for help if something doesn't work properly.

Although my reply might sound impolite, I still believe that - in a long term - you'll benefit from what you learn. Scripts come and go, but knowledge remains.
Re: populating data from temp tables [message #212253 is a reply to message #212201] Thu, 04 January 2007 06:08 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

thanks for advise. I'll try for it.
Re: populating data from temp tables [message #212258 is a reply to message #212253] Thu, 04 January 2007 06:16 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK. Looking forward to see your solution!
Re: populating data from temp tables [message #212456 is a reply to message #212258] Fri, 05 January 2007 06:12 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

I tried using the same code. But, there are few tables does not exists in the specified range.

Moreover, there are different range of tables like 190-198 and then 210-220 so that I need to make chneg each time in this code.

Is there any better solutions?

declare
L_stmt varchar2(900);
L_start int := 150;
begin
FOR J in 150..158 LOOP
L_stmt := 'insert into T'||J||' select * from T'||J||'_TMP' ;
dbms_output.put_line(L_Stmt) ;
execute immediate L_stmt;
END LOOP;
end;

Thanks in advance.
Re: populating data from temp tables [message #212458 is a reply to message #212456] Fri, 05 January 2007 06:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In one of my previous posts, I've said (quote): "in a cursor loop select all tables ...". You have a loop, but it is not a CURSOR loop, nor it selects TABLES.

What I meant was something like this (SUBSTR function selects number from a TMP table name, for example: T140_TMP -> 140):
DECLARE
  l_stmt VARCHAR2(200);
BEGIN
  FOR cur_r IN (SELECT tname FROM TAB 
                WHERE tname LIKE 'T%TMP'
			   )
  LOOP
    l_stmt := 'INSERT INTO t' ||
               SUBSTR(cur_r.tname, 2, INSTR(cur_r.tname, '_', 1) - 2) ||
             ' SELECT * FROM ' ||
               cur_r.tname;
    EXECUTE IMMEDIATE (l_stmt);
  END LOOP;
END;

Check what it does and see could you use it.
Re: populating data from temp tables [message #212476 is a reply to message #212456] Fri, 05 January 2007 08:51 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

As I understand that this script would generate tables incuding records from T140_TMP TO T140 for all the existing tables in the current schema.

i.e. T150_TMP to T150
T151_TMP to T151

Please correct it if I am wrong.

Thanks a lot for your help.
Re: populating data from temp tables [message #212526 is a reply to message #212476] Fri, 05 January 2007 12:15 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No, not for all existing tables in this schema, but all tables whose name is LIKE 'T%TMP'. Those would be T1_TMP, T223_TMP, T140_TMP, etc.

Also, it would NOT create tables (I understand that all tables already ARE created), but insert existing records from TMP tables to those without TMP in their name (T1_TMP -> T1, T223_TMP -> T223, etc.).
If those tables aren't created yet, no problem - just modify the statement - instead of INSERT INTO, you'd have "CREATE TABLE T123 AS SELECT * FROM T123_TMP".
Previous Topic: find out second sunday in March
Next Topic: update a table with values from a different table
Goto Forum:
  


Current Time: Fri Dec 09 19:34:37 CST 2016

Total time taken to generate the page: 0.08969 seconds