Results 1 to 4 of 4

Thread: Help in Insert

  1. #1
    Join Date
    Jun 2005
    Posts
    16

    Unanswered: Help in Insert

    Hi All,

    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.

    Please help me writing this SQL Script.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2005
    Location
    Bangalore,India
    Posts
    39
    Use MERGE statement .This is new feature in UDB V8.Please read SQL COOKBOOK V8 for more information.

  3. #3
    Join Date
    Jun 2005
    Posts
    16

    Help In Insert

    Hi,

    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
    ON SB.OBJECT_NAME=SO.OBJECT_NAME
    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')
    WHEN MATCHED
    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

    Please help me in this.

    Thanks in Advance.

  4. #4
    Join Date
    Feb 2005
    Location
    Bangalore,India
    Posts
    39
    Please use the below MERGE satement.

    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
    FROM SYSIBM.SYSDUMMY1
    ) 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
    ,SECURITY_OBJECT_TYPE_ID
    ,OBJECT_NAME
    ,OBJECT_DESCRIPTION)
    VALUES ( TEMP.APPLICATION_ID
    ,TEMP.SECURITY_OBJECT_TYPE_ID
    ,TEMP.OBJECT_NAME
    ,TEMP.OBJECT_DESCRIPTION )
    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.

    Thanks,
    Nagaraj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •