Solved

How to insert all records from one table to another

Posted on 2004-03-26
4
2,429 Views
Last Modified: 2011-08-18
I hv written procedure which inserts records in particular table .
it does manually after wirting excecution keyword.
But how can i transfer records from one table to another. for ecample i hv table customer and i want to insert all rows of customer table to dub_customer table.

what i have edit in my procedure?
0
Comment
Question by:khushi2003
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 25 total points
ID: 10692873
No need to use procedure for such a simple task

use this:

insert into dub_customer select * from customer;   --if they have the same set of columns


if not, you have to specify columns in the select clause.

0
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 25 total points
ID: 10693453
Or you can create a new table with the same structure and data:

SQL> create table dub_customer  as select * from customer;

Table created.

and as seazodiac, specify columns if not same set:

SQL> desc t2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(5)
 T1ID                                               NUMBER(5)
 VAR                                                VARCHAR2(20)
 D1                                                 DATE

SQL> desc t3;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(2)
 D                                                  VARCHAR2(20)

SQL> insert into t3 select id, var from t2;

6 rows created.

Hope this helps
0
 
LVL 2

Expert Comment

by:n4nazim
ID: 10693642
Hi,

If both the structures are same ...

insert into dub_customers select * from customers;

while if the data structures are different u say

insert into dub_customers select cust_id, cust_name from customers;


can u paste the structures for both the tables here then it would be easy to help on this issue.

Rgds
NHM
0
 
LVL 2

Expert Comment

by:aabbas
ID: 10694161
If you intend to do this simple task using stored procedure (may be you need to do this frequently), you can create a simple procedure like the following.

create or replace procedure copy_data (p_From varchar2, p_To varchar2, p_Create char default 'N') as
  vSQL varchar2 (150);
begin
  savepoint savCopy;
  if p_Create = 'Y' then
    vSQL := 'create table' || p_To || 'as';
  else
    vSQL := 'insert into ' || p_To;
  end if;
  vSQL := vSQL || ' select * from ' || p_From;
  execute immediate vSQL;
  commit;
exception
  when Others then
    rollback to savCopy;
    raise_application_error (-20999, sqlerrm);
end;
/

This will insert all records from FIRST table to SECOND. By default, it assumes that the table already exists thus not need to create. But if you want this procedure to create table as well, pass the THIRD paramter as 'Y' to let the procedure create table for you.

The "matching column" rule is still there. You must make sure that both the tables have identical columns specification.

Hope, it will solve your problem.

Best of luck.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Migrate database to ASM disks. 1 39
File generation using utl_file 4 46
PAYER_ID has both atributes 4 31
help on oracle query 5 31
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now