Solved

How to insert 1000 records throught sql statement

Posted on 2006-07-11
14
2,517 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 

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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to monitor tomcat's heap usage? 2 114
How to use Database Explorer App in Matlab R2011a? 1 301
how to do batch update  in java 4 164
Java Email Transport Not working 14 104
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.…
There are numerous questions about how to setup an IBM HTTP Server to be administered from WebSphere Application Server administrative console. I do hope this article will wrap things up and become a reference for this task. You need three things…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

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