Solved

How to insert all records from one table to another

Posted on 2004-03-26
4
2,428 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

12 Experts available now in Live!

Get 1:1 Help Now