Home » SQL & PL/SQL » SQL & PL/SQL » if condition for inserts (oracle 10g)
if condition for inserts [message #407889] Fri, 12 June 2009 04:05 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,

iam having some insert statements how can i give if condition for these multiple insert statements.

INSERT INTO EMRConsultantSpecialtiesLkup (SPECIALTY_ID,SPECIALTY_NAME,SPECIALTY_CODE,GROUP_ID,STATUS,USER_LOGIN,
CREATED_DATE,LAST_UPDATED_DATE,LAST_UPDATED_USER) values (1013,'ORTHOPAEDICS','OR',NULL,1,NULL,NULL,NULL,NULL)
/
INSERT INTO EMRConsultantSpecialtiesLkup (SPECIALTY_ID,SPECIALTY_NAME,SPECIALTY_CODE,GROUP_ID,STATUS,USER_LOGIN,
CREATED_DATE,LAST_UPDATED_DATE,LAST_UPDATED_USER) values (1014,'CARDIOLOGY','CA',NULL,1,NULL,NULL,NULL,NULL)
/
INSERT INTO EMRConsultantSpecialtiesLkup (SPECIALTY_ID,SPECIALTY_NAME,SPECIALTY_CODE,GROUP_ID,STATUS,USER_LOGIN,
CREATED_DATE,LAST_UPDATED_DATE,LAST_UPDATED_USER) values (1015,'ENT','EN',NULL,1,NULL,NULL,NULL,NULL)
/
INSERT INTO EMRConsultantSpecialtiesLkup (SPECIALTY_ID,SPECIALTY_NAME,SPECIALTY_CODE,GROUP_ID,STATUS,USER_LOGIN,
CREATED_DATE,LAST_UPDATED_DATE,LAST_UPDATED_USER) values (1016,'FOOT SOLUTIONS','FS',NULL,1,NULL,NULL,NULL,NULL)
/
INSERT INTO EMRConsultantSpecialtiesLkup (SPECIALTY_ID,SPECIALTY_NAME,SPECIALTY_CODE,GROUP_ID,STATUS,USER_LOGIN,
CREATED_DATE,LAST_UPDATED_DATE,LAST_UPDATED_USER)
 values (1017,'PHYSIO THERAPY','PT',NULL,1,NULL,NULL,NULL,NULL)
/
INSERT INTO EMRRoleLicensingMatrix (EMR_FEATURE_ID,EMR_FEATURE_NAME,EMR_PARENT_FEATURE_ID,EMR_ORDER_ID,
EMR_LICENSABLE,EMR_DEFAULT_PAGE,EMR_FEATURE_MENU,FRONTDESK,OFFICE_MANAGER,
PHYSICIAN_ASSISTANT,CONSULTANT,ACCOUNTS,DATA_MANAGER,TRANSCRIPTIONIST,
SYSTEM_ADMIN,EZEMRX_ADMIN,EMR_FEATURE_VIEW,STATUS)
 VALUES (168,'Cardiology',-1,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0)
/
INSERT INTO EMRRoleLicensingMatrix (EMR_FEATURE_ID,EMR_FEATURE_NAME,EMR_PARENT_FEATURE_ID,EMR_ORDER_ID,
EMR_LICENSABLE,EMR_DEFAULT_PAGE,EMR_FEATURE_MENU,FRONTDESK,OFFICE_MANAGER,
PHYSICIAN_ASSISTANT,CONSULTANT,ACCOUNTS,DATA_MANAGER,TRANSCRIPTIONIST,
SYSTEM_ADMIN,EZEMRX_ADMIN,EMR_FEATURE_VIEW,STATUS) 
VALUES (169,'ENT',-1,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0)
/
INSERT INTO EMRRoleLicensingMatrix (EMR_FEATURE_ID,EMR_FEATURE_NAME,EMR_PARENT_FEATURE_ID,EMR_ORDER_ID,
EMR_LICENSABLE,EMR_DEFAULT_PAGE,EMR_FEATURE_MENU,FRONTDESK,OFFICE_MANAGER,
PHYSICIAN_ASSISTANT,CONSULTANT,ACCOUNTS,DATA_MANAGER,TRANSCRIPTIONIST,
SYSTEM_ADMIN,EZEMRX_ADMIN,EMR_FEATURE_VIEW,STATUS) 
VALUES (170,'Foot Solutions',-1,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0)
/
INSERT INTO EMRRoleLicensingMatrix (EMR_FEATURE_ID,EMR_FEATURE_NAME,EMR_PARENT_FEATURE_ID,EMR_ORDER_ID,
EMR_LICENSABLE,EMR_DEFAULT_PAGE,EMR_FEATURE_MENU,FRONTDESK,OFFICE_MANAGER,
PHYSICIAN_ASSISTANT,CONSULTANT,ACCOUNTS,DATA_MANAGER,TRANSCRIPTIONIST,
SYSTEM_ADMIN,EZEMRX_ADMIN,EMR_FEATURE_VIEW,STATUS) 
VALUES (171,'Physio Therapy',-1,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0)
/
INSERT INTO EMRLicensedSpecialtiesLkup (SPECIALTY_ID,EMR_FEATURE_ID,STATUS) VALUES (1014,168,1)
/
INSERT INTO EMRLicensedSpecialtiesLkup (SPECIALTY_ID,EMR_FEATURE_ID,STATUS) VALUES (1015,169,1)
/
INSERT INTO EMRLicensedSpecialtiesLkup (SPECIALTY_ID,EMR_FEATURE_ID,STATUS) VALUES (1016,170,1)
/
INSERT INTO EMRLicensedSpecialtiesLkup (SPECIALTY_ID,EMR_FEATURE_ID,STATUS) VALUES (1017,171,1)
/


[Cut data into short line length chunks]

[Updated on: Fri, 12 June 2009 05:11] by Moderator

Report message to a moderator

Re: if condition for inserts [message #407890 is a reply to message #407889] Fri, 12 June 2009 04:16 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Please edit your post so that your code does not exceed 80 characters per line.
It becaome a real pain to follow a thread when you have to keep scrolling left and right.

Report message to a moderator

Re: if condition for inserts [message #407891 is a reply to message #407889] Fri, 12 June 2009 04:17 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
What do you mean by if conditions?
Put them in an anonymous block.

By
Vamsi

Report message to a moderator

Re: if condition for inserts [message #407893 is a reply to message #407891] Fri, 12 June 2009 04:21 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
like how can i proceed that can you show with an example

Report message to a moderator

Re: if condition for inserts [message #407897 is a reply to message #407893] Fri, 12 June 2009 04:37 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi can anyone give me the anonymous block of doing so or help me how to proceed using insert all and not in commands for if condition.

Report message to a moderator

Re: if condition for inserts [message #407898 is a reply to message #407893] Fri, 12 June 2009 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Show what?

Regards
Michel

Report message to a moderator

Re: if condition for inserts [message #407901 is a reply to message #407897] Fri, 12 June 2009 04:41 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Doesn't seem like there is any point in showing you anything, since you can't even follow a simple request to reformat your code so that it makes the entire thread easier to work with, nor can you gather your thoughts well enough to actually let us know what it is you are actually trying to do.
Still, I wish you good luck in getting your answer.

Report message to a moderator

Re: if condition for inserts [message #407902 is a reply to message #407901] Fri, 12 June 2009 04:52 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
for the above insert statements like based on
if the record exists and not exists we have to take care.so what if condition i required.

Report message to a moderator

Re: if condition for inserts [message #407904 is a reply to message #407902] Fri, 12 June 2009 05:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you want to do if the record you are trying to insert already exists?

Report message to a moderator

Re: if condition for inserts [message #407906 is a reply to message #407904] Fri, 12 June 2009 05:11 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i have to add if condition for all the insert statements as condition should be based on the record exists or not.if exists it should not.if not it should insert.that is the requirement.
as i heard using insert on exists condition but i am not that much sure how to usage that

Report message to a moderator

Re: if condition for inserts [message #407907 is a reply to message #407906] Fri, 12 June 2009 05:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use the Error Logging Clause on the INSERT statement to trap errors and log the details to an error table.

You could use MERGE instead of INSERT, and ommit the UPDATE section of the merge

You could (assuming that you have a pk column on the tables) just do nothing - if you run that as a script in SQL*Plus all the rows that don't insert will raise errors, and the rows that do insert will still work.

What you're asking for (a pl/sql based approach that does a select for each row) is less efficient than any of the above solutions.

Report message to a moderator

Re: if condition for inserts [message #407909 is a reply to message #407907] Fri, 12 June 2009 05:39 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can i use like this way

INSERT INTO EMRConsultantSpecialtiesLkup (SPECIALTY_ID,SPECIALTY_NAME,SPECIALTY_CODE,GROUP_ID,STATUS,USER_LOGIN,CREATED_DATE,LAST_UPDATED_DATE,LAST_UPDATED_USER)
SELECT (1013,'ORTHOPAEDICS','OR',NULL,1,NULL,NULL,NULL,NULL)
FROM dual WHERE not exists (select * from EMRConsultantSpecialtiesLkup where EMRConsultantSpecialtiesLkup.SPECIALTY_ID = 1013)
/


will it work for me

Report message to a moderator

Re: if condition for inserts [message #407930 is a reply to message #407909] Fri, 12 June 2009 07:23 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would work, but would be slower than
BEGIN
 INSERT INTO.....
EXCEPTION
  WHEN dup_val_on_index THEN
    null;
END;

Report message to a moderator

Previous Topic: Issue in "connect by level" (merged 3)
Next Topic: ORA:28234 - 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Dec 13 00:01:24 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.