Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2443
  • Last Modified:

How to insert all records from one table to another

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
khushi2003
Asked:
khushi2003
2 Solutions
 
seazodiacCommented:
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
 
baonguyen1Commented:
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
 
n4nazimCommented:
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
 
aabbasCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now