Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to insert all records from one table to another

Posted on 2004-03-26
4
Medium Priority
?
2,439 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 100 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 100 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

885 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