Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Create a script to insert entry

SOLVED
Go to solution
Ratzie
Super Advisor

Create a script to insert entry

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.
6 REPLIES
Michael Schulte zur Sur
Honored Contributor
Solution

Re: Create a script to insert entry

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
Honored Contributor

Re: Create a script to insert entry

Oops,

forgot:
update blabla set LOGIN='NEWDEV';

greetings,

Michael
Hein van den Heuvel
Honored Contributor

Re: Create a script to insert entry

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
Honored Contributor

Re: Create a script to insert entry



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
Honored Contributor

Re: Create a script to insert entry

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Yogeeraj_1
Honored Contributor

Re: Create a script to insert entry

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)