Create a script to insert entry

[Ratzie]

Heres my scenerio,

This is the select statement to show all the table...
select *
from CODE_LOGIN
order by CODE;


CODE TYPE LOGIN_ID PASSWORD ENABLED
ASHRM, SWITCH, log, 5555, 1
BCHTM, SWITCH, log, 5555, 1
BDVNM, SWITCH, log, 5555, 1
BNRVM, SWITCH, log, 5555, 1
BRNDM, SWITCH, dev, april, 1
DAPHM, SWITCH, dev, april, 1
FSBRM, SWITCH, log, 5555, 1
GDRPM, SWITCH, log, 5555, 1
GDRVM, SWITCH, log, 5555, 1
(there is alot more, 70)


I then made a script and that would only show the CODE if it matches 'dev'

select CODE
from CODE_LOGIN
where LOGIN_ID = 'DEV'
order by CODE;

BRNDM
DAPHM
...
(about 50 entries)
I have this saved to a file. All the CODES that have a login_id of 'dev'


Now the big part...
How do I insert a new record, basically I am adding another login_id to the CODE. But, only if the CODE had the dev login id.
Can I do a for loop to look at the file that I saved, and insert values into table.

SQL> insert into CODE_LOGIN (CODE, TYPE, LOGIN_ID, PASSWORD, ENABLED)
2 values (CODE(??),'SWITCH', 'DEV', 'APRIL', 1);


The ? is where the problem is... How do I get the CODE? There are about 50 entries, but they will have the same user_id and passwd inserted in all 50.


Any help would be appreciated.

[Michael Schulte zur Sur · Accepted solution]

Hi,

my idea would be:
create a temporary table with
create table blabla as
select from CODE_LOGIN where LOGIN_ID = 'DEV'
insert into table CODE_LOGIN as select from blabla;
drop table blabla

I hope, that works,

Michael

[Michael Schulte zur Sur]

Oops,

forgot:
update blabla set LOGIN='NEWDEV';

greetings,

Michael

[Hein van den Heuvel]

Since you have teh target 'code' values already in a file, you could post-process that file with awk or perl to generate the desired sql statements.

There are many neat SQL solutions.
Bu in line with you tem file, here are some ideas:

For example, assume the file is called a.dat
Now create a.awk containing:
{print "insert into CODE_LOGIN (CODE, TYPE, LOGIN_ID, PASSWORD, ENABLED) values ('" $1 "','SWITCH','DEV','APRIL',1);"}

Now execute:
awk -f a.awk < a.dat > a.sql
and
sqlplus @a.sql

In perl you can of course readily piped the morphed input data straight into sqlplus, which coudl be interesting for frequently repeated transformation.

Or you could use sql to generate sql...

select 'insert into CODE_LOGIN (CODE, TYPE, LOGIN_ID, PASSWORD, ENABLED)' ||
' values (''' || code ||
''',''SWITCH'',''DEV'',''APRIL'',1);'
from test where login_id = 'DEV'
/


You have set sqlplus NOT to print header/footers or post-edit the spool file.

The ugly part is all those single quotes as we need a double-single quote in a quoted string. The above SQL takes an string with 'insert...' adds the current field value of code and concatenates the other values. You then execute the generated output. Good fun!

Enjoy,
Hein.

[Hein van den Heuvel]

Mostly you should be able to do all this directyl witha single statement. I'm not sure I fully understand your requirement but it could be something like:

insert into code_login
select code, 'switch', 'dev', 'APRIL', 1
from code_login
where login_id = 'dev';

I used "'switch'" as per your partial example.
But if you wanted the same value as the original record, then just use "type".

Hein.

[Sanjay Kumar Suri]

Try the following example which uses constraints:

create table temp1 (code char(5), login_id char(3));
insert into temp1 values ('AAAA', 'DEV');
insert into temp1 values ('BBBB', 'DEV');
insert into temp1 values ('CCCC', 'PRD');

create table temp2 as select * from temp1 where login_id='DEV';

alter table temp1 add constraint constraint_name1 primary key (code);
alter table temp2 add constraint constraint_name2 primary key (code);
alter table temp1 add constraint constraint_name3 foreign key (code) references temp2 (code);

delete from temp1;
insert into temp1 values ('AAAA', 'DEV');

sks

[Yogeeraj_1]

hi,

try:

insert into code_login (code, type, login_id, password, enabled)
select code||'2', type, login_id, password, enabled
from code_login
where login_id = 'DEV';


where you can change the << code||'2' >> as desired.

hope this helps

regards
Yogeeraj