I have a Table Named SECURITY_OBJECT with 4 columns.
in that a column named OBJECT_NAME (VARCHAR) has Values like CUSTOMER, EMPLOYEE.
Now i would like to write a query to check whether the values CUSTOMER, Employee exists in the Table or not. If the values are not there in the table then i want the script to insert these Values into the Database.
else if the values are present it should not insert.
As you refered me to check the SQL Cook book, I have used the Merge but i am getting the error.
This is the syntax i ahve used:
MERGE INTO SECURITY_OBJECT SB
USING SECURITY_OBJECT SO
WHEN NOT MATCHED THEN
INSERT INTO SECURITY_OBJECT SB (APPLICATION_ID, SECURITY_OBJECT_TYPE_ID, OBJECT_NAME, OBJECT_DESCRIPTION) VALUES (1, 13, '
AuditTrail', 'AuditTrail screens')
AND SO.OBJECT_NAME='AuditTrail' THEN
SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'RECORD ALREADY EXISTS';
When i execute the above i am getting the error as
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "SECURITY_OBJECT" was found following "MERGE
INTO ". Expected tokens may include: "JOIN". SQLSTATE=42601
Here replace IN_APPLICATION_ID with INT(1)
IN_SECURITY_OBJECT_TYPE_ID with INT(13)
IN_OBJECT_NAME with CHAR('AuditTrail')
IN_OBJECT_DESCRIPTION with CHAR('AuditTrail screens')
MERGE INTO SECURITY_OBJECT AS A
USING ( SELECT IN_APPLICATION_ID AS APPLICATION_ID
,IN_SECURITY_OBJECT_TYPE_ID AS SECURITY_OBJECT_TYPE_ID
,IN_OBJECT_NAME AS OBJECT_NAME
,IN_OBJECT_DESCRIPTION AS OBJECT_DESCRIPTION
) AS TEMP
ON A.APPLICATION_ID = TEMP.APPLICATION_ID -- Assumed that APPLICATION_ID is the primary key
WHEN MATCHED THEN
SIGNAL SQLSTATE '23505' SET MESSAGE_TEXT = 'Primary Key constraint voilation'
WHEN NOT MATCHED AND OBJECT_NAME <> 'CUSTOMER' AND OBJECT_NAME <> 'EMPLOYEE' THEN
INSERT ( APPLICATION_ID
VALUES ( TEMP.APPLICATION_ID
WHEN NOT MATCHED AND (OBJECT_NAME = 'CUSTOMER' OR OBJECT_NAME = 'EMPLOYEE') THEN
SIGNAL SQLSTATE ’70001’ SET MESSAGE_TEXT = 'Record already exist in the table';
It is better to write a procedure with the above code and call the procedure in your script.