Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2009
    Posts
    21

    Question Unanswered: How to test values before insert

    Hi,
    does anybody know how to test in script files if values already exists in a table(s), please?

    I have following data.sql script:
    ---
    CONNECT TO MyDB;
    INSERT INTO APPCONFIG (KEY, VALUE) VALUES ('someKey', 'someValue');
    CONNECT RESET;
    ---
    (APPCONFIG is my custom table)

    I run this script using DB2 command line tool:
    ---
    DB2 -tf data.sql
    ---

    My problem is, that this script may be run more than once. So i need to add some IF statement to test if the key alerady exists. I've tried a lot of IF SELECT COUNT(*) FROM ... statements, but I still receive syntax errors etc.

    Can anybody help me, please?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS?

    Andy

  3. #3
    Join Date
    Apr 2009
    Posts
    21
    Sorry, sure - DB2 v9.5.0.808 (Express), OS Win 2003 Server
    (I thought the language is version and OS independent).

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I wanted the version info so I know what features are available to you.

    Why do you want to test the existence first? Why not just let it fail?

    Andy

  5. #5
    Join Date
    Apr 2009
    Posts
    21
    Thank you for your reply.

    The KEY column is not a PK, there is also ID (generated as identity) and the code will not fail, but insert new row into the table. Sure, in this scenario the KEY could be a PK, but I have also tables as USERS, ROLES, EMPLOYEES etc that I also need to pre-fill by the same script.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Take a look at the MERGE statement to use instead of the INSERT statement.

    Andy

  7. #7
    Join Date
    Apr 2009
    Posts
    21
    I alraedy did, but it seems to me the MERGE statement meges data from another /source/ table, not from defined values, doesn't?

    Thanks a lot!

    Frantisek.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can use MERGE from defined values, something like:


    MERGE into MyTable as m using (values (cast('somekey' as varchar(12)) as KEY, cast('somevalue' as varchar(20)) as VALUE) ) as x on (m.key = x.key)
    WHEN NOT MATCHED THEN insert...


    Andy

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if statements are not allowed in db2 scripts
    on unix use shell scripting to do or on windows any other supported language todo so. there is something like dos scripting.. or perl..
    or use a stored procedure
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Andy's code must be syntax error of naming columns in values clause.
    Correction would like:

    MERGE into MyTable as m using (values (cast('somekey' as varchar(12)), cast('somevalue' as varchar(20))) ) as x(key, value) on (m.key = x.key)
    WHEN NOT MATCHED THEN insert...

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you have a unique constraint on the KEY column, just ignore the error. If you have not a unique constraint, create it.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by przytula_guy
    if statements are not allowed in db2 scripts
    I think you can you use the Begin atomic compound stmts in scripts
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  13. #13
    Join Date
    Apr 2009
    Posts
    21
    That's it! Thank you both.

  14. #14
    Join Date
    Apr 2009
    Posts
    21
    Quote Originally Posted by rahul_s80
    I think you can you use the Begin atomic compound stmts in scripts
    I've solved my primary problem by MERGE statement, but I'm newbie in DB2 world, can you please explain what do you mean by BEGIN ATOMIC statement?

    Thanks a lot!

  15. #15
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    You can conditional stmts in a begin atomic script as :

    /TEMP 21$ cat 1.txt
    Begin Atomic

    if ( select count(*) from dim.test4 ) = 0
    then
    insert into dim.test4 values (4);
    else
    update dim.test4 set no=no+10 ;
    end if;

    end @

    /TEMP 24$ db2 -td@ -f 1.txt
    DB20000I The SQL command completed successfully.
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

Posting Permissions

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