Solved

How to insert 1000 records throught sql statement

Posted on 2006-07-11
14
2,527 Views
Last Modified: 2013-12-01
Dears i want to insert 1000 records by only one query. I want to make something like loop we use in language to perform any action in a particular number of times.

insert into ali_bill(cnsg_no,cus_no,cus_nam)
values ('1' to '1000',P0110','ALI')

I want to insert these values for one thousand time but by only one sql statement and in only one action.

Do help me out.

Ali Haider
0
Comment
Question by:ALIHAIDER1
[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
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17081977
>I want to insert these values for one thousand time but by only one sql statement and in only one action.
The fastest way that I could think of to pull this off would be to create a temp table with 1000 records, then refer to it in your one query..

INSERT INTO ali_bill(snsg_no, cus_no, cus_name
SELECT id, 'P0110', 'ALI' FROM YourTempTable
0
 
LVL 3

Expert Comment

by:jazzyline
ID: 17084606
ALIHAIDER1,

Here is what you need to do:

DECLARE
v_var NUMBER := 1;
BEGIN
FOR i in 1 .. 1000
LOOP
v_var := v_var +1;
INSERT INTO ali_bill(cnsg_no,cus_no,cus_nam)
VALUES (v_var,'P0110','ALI');
END LOOP;
END;

Just run this program. This will insert numbers 1 to 1000 in the cnsg_no column. The 'P0110', 'ALI' will be inserted as constant values in all the rows.
What I have done is declared a variable v_var having number as it's data-type. I loop through this variable 1000 times and increment it's value each time by 1. So if you want to insert more records, you just need to increase the number in the for loop.

HTH
 
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17085727
insert into ali_bill(cnsg_no,cus_no,cus_nam)
  select cnt, 'P0110','ALI'
    from (select level cnt from dual connect by level <= 1000)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:ALIHAIDER1
ID: 17087984
SQL> declare
  2  v_var number:=1111111111;
  3  begin
  4  for i in 1111111111 .. 1111111120
  5  loop
  6  v_var:=v_var+1;
  7  insert into oms_cnsg_bill(cnsg_no,cus_no,rpt_date,bkg_dat)
  8  values (v_var,'P0018','12/07/2006','12/07/2006')
  9  end loop;
 10  end;
 11  
 11  

Dear how to terminate or final the statement. I press enter but it just jump to another line.
I made this as a specimen.

Ali Haider
0
 

Author Comment

by:ALIHAIDER1
ID: 17088015
SQL> insert into oms_cnsg_bill(cnsg_no,rpt_date)
  2  select cnt,'12/07/2006' from (
  3  select level cnt from dual connect by level <10);
insert into oms_cnsg_bill(cnsg_no,rpt_date)
            *
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data

Kindly see the error and guide me again

Ali Haider
0
 
LVL 6

Expert Comment

by:DLyall
ID: 17089080
insert into ali_bill(cnsg_no, cus_no, cus_nam)
select row_count.cnt, 'P0110','ALI'
from (select level cnt from dual connect by level <= 1000) row_count
0
 

Author Comment

by:ALIHAIDER1
ID: 17090110
SQL> insert into oms_cnsg_bill(cnsg_no,rpt_date)
  2  select cnt,'12/07/2006' from (
  3  select level cnt from dual connect by level <10) row_count;
insert into oms_cnsg_bill(cnsg_no,rpt_date)
            *
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data

Dear the error remains same. Kindly do help me out again.

Ali
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17090201
insert into ali_bill(cnsg_no,cus_no,cus_nam)
(select level, ' 'P0110','ALI' from dual connect by level < 1000);
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 125 total points
ID: 17090323
Ignore last post use this instead
insert into ali_bill(cnsg_no,cus_no,cus_nam)
select a.*,'P0110','ALI' from (select level from dual connect by level < 1000) a
0
 
LVL 3

Assisted Solution

by:jazzyline
jazzyline earned 125 total points
ID: 17092484
Ali,
If you are using sql*plus then at the end of the program press enter and then type / (forward slash) and press enter again. That will execute this program.
Make sure the column names in the insert section are exactly the same as they appear in the table.

declare
v_var number :=1111111111;
begin
for i in 1 .. 20
loop
v_var:=v_var+1;
insert into oms_cnsg_bill(cnsg_no,cus_no,rpt_date,bkg_dat)
values (v_var,'P0018','12-JUL-2006','12-JUL-2006');
end loop;
commit;
end;
/

Copy and  paste the above code and run it. If you hit an error, copy and paste the error message here.
0
 
LVL 1

Expert Comment

by:scathe
ID: 17174672
just use

---------------------------------
begin
for i in 1..1000 loop
    INSERT INTO ali_bill(snsg_no, cus_no, cus_name
    values
    (i, 'P0110', 'ALI');
end loop;
end;
/
---------------------------------
if all you want is 1000 rows of the same data except for the incremented ID value

COMMIT; to make it permanent
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 17241582
You can also do this:

CREATE TABLE ALI_BILL( cnsg_no NUMBER,cus_no CHAR(5),cus_nam CHAR(3))
/

Table created.

insert into ali_bill(cnsg_no,cus_no,cus_nam)
SELECT huns.x+tens.x+ones.x+1, 'P0110','ALI' FROM
(SELECT 0 x FROM dual
UNION SELECT 1 FROM dual
UNION SELECT 2 FROM dual
UNION SELECT 3 FROM dual
UNION SELECT 4 FROM dual
UNION SELECT 5 FROM dual
UNION SELECT 6 FROM dual
UNION SELECT 7 FROM dual
UNION SELECT 8 FROM dual
UNION SELECT 9 FROM dual) ones,
(SELECT 0 x FROM dual
UNION SELECT 10 FROM dual
UNION SELECT 20 FROM dual
UNION SELECT 30 FROM dual
UNION SELECT 40 FROM dual
UNION SELECT 50 FROM dual
UNION SELECT 60 FROM dual
UNION SELECT 70 FROM dual
UNION SELECT 80 FROM dual
UNION SELECT 90 FROM dual) tens,
(SELECT 0 x FROM dual
UNION SELECT 100 FROM dual
UNION SELECT 200 FROM dual
UNION SELECT 300 FROM dual
UNION SELECT 400 FROM dual
UNION SELECT 500 FROM dual
UNION SELECT 600 FROM dual
UNION SELECT 700 FROM dual
UNION SELECT 800 FROM dual
UNION SELECT 900 FROM dual) huns
/

1000 rows created.


COMMIT;
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Most of the developers using Tomcat find it easy to configure the datasource in Server.xml and use the JNDI name in the code to get the connection.  So the default connection pool using DBCP (or any other framework) is made available and the life go…
Upgrading Tomcat – There are a couple of methods to upgrade Tomcat is to use The Apache Installer is to download and unzip and run the services.bat remove|install Tomcat6 Because of the App that we are working with, we can only use Tomcat 6.…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

627 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