Results 1 to 10 of 10

Thread: INSERT ignore

  1. #1
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58

    Unanswered: INSERT ignore

    Is there an equivalent to mySQL INSERT IGNORE. It will try to insert the row, if it finds a duplicate key it will fail gracefully.

    EXAMPLE:
    create table test (num SMALLINT NOT NULL, primary key (num));

    insert into test values (123);
    insert into test values (123); <-- won't cause an error.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You will always get an SQL return code in DB2. How you choose to deal with the return code that is negative (indicating an error) depends on what interface you are using (embedded SQL, jdbc, cli, stored procedure, etc).

    For example, with SQL stored procedures, you can use a Continue Handler and Exit Handler to control how the application functions when getting a non-zero SQL return code (negative for errors, positive for warnings).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    What about syntax that would to an INSERT, but if its already in the table do an UPDATE.

    What i found so far was:

    MERGE INTO MyTEST AS test
    USING (SELECT DISTINCT col FROM MyTEST2 WHERE col = 'update') AS test2
    ON test.col = test2.col
    WHEN MATCHED THEN
    UPDATE SET (col) = 'updated the column'
    WHEN NOT MATCHED THEN
    INSERT (id,col) VALUES (CURRENT TIMESTAMP,'inserted into table')

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You might want to look at the IMPORT command which has similar functionality to the MERGE using a sequential input file.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    Unfortunatly using the IMPORT via a file isn't an option.

    The only other way i can think of is doing an UPDATE, then an INSERT ... IF NOT EXISTS()..

    That way it will UPDATE it if its there, but if its not it will not UPDATE and pass the IF NOT EXISTS and INSERT the data. The only problem with this method is that it will only work with INSERT .. SELECT syntax, not just a straight INSERT.

    Does anyone know of a better solution to this problem? I'm sure an UPDATE if there, or INSERT if not is a common problem.

  6. #6
    Join Date
    Jul 2004
    Posts
    53
    Quote Originally Posted by Marcus_A
    You will always get an SQL return code in DB2. How you choose to deal with the return code that is negative (indicating an error) depends on what interface you are using (embedded SQL, jdbc, cli, stored procedure, etc).

    For example, with SQL stored procedures, you can use a Continue Handler and Exit Handler to control how the application functions when getting a non-zero SQL return code (negative for errors, positive for warnings).
    How do I go about using those handlers to drop a table if exists and re-create it (always). I tried the following:

    create procedure test.testsp ( )
    language sql

    spmain: begin

    declare continue handler for sqlexception
    begin
    drop table test.testtable;
    end;

    create table test.testtable (
    testfield char (10)
    );

    end spmain


    ... but I get an error when I build the sp saying a table with the same name as the one I am trying to create exists. Of course it exists, but I am dropping it before. Are there settings to tell the builder not to look into that detail?

    TIA!

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    How about this ??

    Cheers
    Sathyaram

    Code:
    CREATE PROCEDURE PROCEDURE2 ( IN TSCH CHAR(10),IN TNAM CHAR(20)  )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
       DECLARE TAB_FOUND      INTEGER      DEFAULT 0;
    
    
    
    SELECT 1 INTO TAB_FOUND FROM SYSIBM.SYSDUMMY1 WHERE EXISTS(SELECT 1 FROM SYSCAT.TABLES WHERE TABNAME=TNAM and TABSCHEMA=TSCH and TYPE='T');
    
    
    
     IF (TAB_FOUND=1)
        THEN
         DROP TABLE TAB1 ;
     END IF ;
     CREATE TABLE TAB1 ( I INT NOT NULL, J INT) ;
    
       
    END P1
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jul 2004
    Posts
    53
    It's exactly the same.

    When you build the sp in the DB2 Development Center, you get the following error:

    Create stored procedure returns -601.

    test.testsp: 11: [IBM][CLI Driver][DB2/NT] SQL0601N The name of the object to be created is identical to the existing name "TEST.TESTTABLE" of type "TABLE". LINE NUMBER=11. SQLSTATE=42710


    When the builder find the CREATE TABLE statement, it complains because it already exists. It does not see it is being dropped before it is being created. The error happens while building.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why not something like (dynamic SQL):

    CREATE PROCEDURE PROCEDURE2 ( IN TSCH CHAR(10),IN TNAM CHAR(20) )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE TAB_FOUND INTEGER DEFAULT 0;

    DECLARE stmt varchar(100);


    SELECT 1 INTO TAB_FOUND FROM SYSIBM.SYSDUMMY1 WHERE EXISTS(SELECT 1 FROM SYSCAT.TABLES WHERE TABNAME=TNAM and TABSCHEMA=TSCH and TYPE='T');



    IF (TAB_FOUND=1)
    THEN
    SET stmt = 'DROP TABLE ' || TSCH || '.' || TNAM;
    EXECUTE IMMEDIATE stmt;
    END IF ;

    SET stmt = 'CREATE TABLE TAB1 ( I INT NOT NULL, J INT)' ;
    EXECUTE IMMEDIATE stmt;


    END P1

    Andy

  10. #10
    Join Date
    Jul 2004
    Posts
    53
    Totally dirty, as I need to repeat this for a bunch of tables, but it worked!

    Thanks once again, Andy!

Posting Permissions

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