topic Re: Create a script to insert entry in Operating System - HP-UX

Create a script to insert entry

Ratzie — Mon, 12 Jan 2004 15:56:35 GMT

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.

Re: Create a script to insert entry

Michael Schulte zur Sur — Mon, 12 Jan 2004 16:14:32 GMT

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

Re: Create a script to insert entry

Michael Schulte zur Sur — Mon, 12 Jan 2004 16:23:03 GMT

Oops,

forgot:
update blabla set LOGIN='NEWDEV';

greetings,

Michael

Re: Create a script to insert entry

Hein van den Heuvel — Mon, 12 Jan 2004 17:31:13 GMT

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.

Re: Create a script to insert entry

Hein van den Heuvel — Tue, 13 Jan 2004 00:28:30 GMT

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.

Re: Create a script to insert entry

Sanjay Kumar Suri — Tue, 13 Jan 2004 00:57:02 GMT

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

Re: Create a script to insert entry

Yogeeraj_1 — Tue, 13 Jan 2004 01:25:20 GMT

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