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

x
?
Solved

How to insert 1000 records throught sql statement

Posted on 2006-07-11
14
Medium Priority
?
2,530 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Configure Web Service (server application) I. Configure security for Web Services methods First, we need to protect Session bean which implements the service: 1. Open EJB deployment descriptor (ejb-jar.xml) in the EJB project that contains you…
This exercise is about for the following scenario: Dmgr and One node with 2 application server. Each application server contains it owns application. Application server name as follows server1 contains app1 server2 contains app1 Prereq…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

916 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