how to Insert 100 of values in single shot in oracle database

can you show me the example how u want to do?

hi chaituu:

insert into t_user_license values('1152546',41129,'1-jul-07','04-nov-08');

like 41129 i have 100's of licenseid's for thos id's i what to change expiry date

can't u write update statment instae of insert.

update  t_user_license t
set t.exprity_date=''
where licenseid's in();

else u need to write cursor like this;

declare
cursor c1 is
select  license_id,expiry_date from table t1;

begin
for r1 in c1
loop
insert into t_user_license values('1152546',r1.license_id,'1-jul-07',r1.expiry_date);
end loop
end;

declare
cursor c1 is
select distinct (licenseid) from t_group_association where group_id=6133 and licenseid between '41129' and '41143';

begin
for r1 in c1
loop
insert into t_user_license values('1152546',r1.licenseid,'1-jul-07','04-nov-08');
end loop;
end;


ERROR at line 8:
ORA-06550: line 8, column 48:
PLS-00302: component 'LICENSEID' must be declared
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored


what to do?

declare
cursor c1 is
select distinct (licenseid) licenseid  from t_group_association where group_id=6133 and licenseid between '41129' and '41143';


i have given some alais name to linceseid ;

how to execute.

are you using any tool or sql plus r u using.

sql plus

declare
cursor c1 is
select distinct (licenseid) licenseid from t_group_association where group_id=6133 and licenseid between '41129' and '41143';

begin
for r1 in c1
loop

if you are  getting error with below insert statment.then comment that statment and put this line and check whether values are printing or not.

DBMS_OUTPUT.PUT_LINE(r1.licenseid);
--insert into t_user_license values('1152546',r1.licenseid,'1-jul-07','04-nov-08');
end loop;
end;

/

hi
PL/SQL procedure successfully completed.

how to execute i m asking.

declare
cursor c1 is
select distinct (licenseid) licenseid from t_group_association where group_id=6133 and licenseid between '41129' and '41143';

begin
for r1 in c1
loop

-insert into t_user_license values('1152546',r1.licenseid,'1-jul-07','04-nov-08');
end loop;
commit';
end;

put commit at the end of end loop so data will be committed.

if you want to do it in a single shot, why not this?

note, it would be better practice to add the column names on the insert


insert into t_user_license(columna,columnb,columnc,columnd)

and, if the last 2 columns are really dates,  then use an explicit date mask and convert them, using 4 digit years

to_date('01-JUL-2007','DD-MON-YYYY'), to_date('04-NOV-2008','DD-MON-YYYY')

insert into t_user_license values
(select distinct '1152546', licenseid,,'1-jul-07','04-nov-08' from t_group_association where group_id=6133 and licenseid between '41129' and '41143')

Select allOpen in new window

hi chaituu:

thanks the proc ? the proc working fine!  now one more requirement! u can see the Insert command the first  column ('1152546' .......) is one userid ... like this i have 100 of userids can u give me any way do this? for each userid i wat to cut and past the proc each time ... any easy way?


thanks yar..

where are these userid's are coming from;i mean from which table?

chaituu:

i got u r point? the table is t_user , but under this table lots of users r there ... the team have picked  some of users to set expiry date ...  let say in the month of July 100 user and August 150 users like that ... But they give me list of userids to set expiry date.

thanks

why do you want to do this procedurally?

Is there a problem with doing it in a single statement as I showed above?

It will consume fewer resources and should be drastically faster.

sdstuber,

whatever approach u specified above is good if he wants to insert data for 100 license id's but apart from that he wants to insert data for specified userid's.then i dont think with the above solution u proposed its not possible thats why i asked him to create a tempararory table to insert selected userids.


cursor c2 is
select userid  from t_temp_users;

begin
for r2 in c2
loop
insert into t_user_license values
(select distinct r2.userid, licenseid,,'1-jul-07','04-nov-08' from t_group_association where group_id=6133 and licenseid between '41129' and '41143')

end loop;
end;