Solved

How to insert 1000 records throught sql statement

Posted on 2006-07-11
14
2,516 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
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 65

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
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.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This article is about some of the basic and important steps to be used to improve the performance in web-sphere commerce application development. 1) Always leverage the Dyna-caching facility provided by the product 2) Remove the unwanted code …
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.…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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