Solved

How to insert 1000 records throught sql statement

Posted on 2006-07-11
14
2,511 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
Comment Utility
>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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ALIHAIDER1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:ALIHAIDER1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

-Xmx and -Xms are the two JVM options often used to tune JVM heap size.   Here are some common mistakes made when using them:   Assume BigApp is a java class file for the below examples. 1.         Missing m, M, g or G at the end …
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.…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now