Solved

How to insert all records from one table to another

Posted on 2004-03-26
4
2,432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

740 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