on 03-12-2014 3:06 PM
Hi together,
i'm facing a problem while using procedures to copy different data from one table to another and hope that somebody can help me fast.
For testing, i created two tables:
CREATE COLUMN TABLE "ADAM_TEST"."TEST1" ("ID" INTEGER CS_INT NOT NULL ,
"VALUE" VARCHAR(255),
PRIMARY KEY ("ID"));
CREATE COLUMN TABLE "ADAM_TEST"."TEST2" ("ID" INTEGER CS_INT NOT NULL ,
"VALUE" VARCHAR(255),
"STREET" VARCHAR (255) null,
"ZIP" VARCHAR (255) null,
PRIMARY KEY ("ID"));
Here is my procedure:
CREATE PROCEDURE _SYS_BIC.WRITE_TEST ( )
LANGUAGE SQLSCRIPT
AS
BEGIN
statement = SELECT ID,VALUE FROM ADAM_TEST.TEST1;
INSERT INTO ADAM_TEST.TEST2 (ID,VALUE) SELECT ID,VALUE FROM :statement ;
END;
Now i want to copy the rows (ID,VALUE) from Test1 to table Test2, but everytime i get an error:
_SYS_BIC.WRITE_TEST: line 10 col 1: [301] (range 3) unique constraint violated exception: unique constraint violated: Table (TEST2).
I read the manual from SAP and tried to find another discussion with a similar error, i also tried to change the query like make the last part:
SELECT * FROM :statement;
but nothing helps.
If i delete the two additional rows (STREET,ZIP) from table definition and try the procedure, it works!
So what's the error here? The table types are the same....
Thanks a lot for your help.
Hi Adam,
Whenever you call procedure for the second time, you will always get unique constraint violation as TEST1 records should not be in TEST 2 table.
One way can be:
BEGIN
statement = SELECT ID,VALUE FROM ADAM_TEST.TEST1;
delete from ADAM_TEST.TEST2;
INSERT INTO ADAM_TEST.TEST2 (ID,VALUE) SELECT ID,VALUE FROM :statement ;
END;
It depends upon what exactly is your requirement and modification needs to be done.
Regards
Raj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.