Solved: copy rows from one table to another

Former Member · ‎03-12-2014

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.

rindia · ‎03-12-2014

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

copy rows from one table to another