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.

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).

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')

You might want to look at the IMPORT command which has similar functionality to the MERGE using a sequential input file.

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.

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!

How about this ??

Cheers
Sathyaram

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.

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

Totally dirty, as I need to repeat this for a bunch of tables, but it worked!

Thanks once again, Andy!