Dynamic insert statement to update cross schema tables

Former Member — Wed, 30 Dec 2015 16:15:58 GMT

Can any one help me to write a dynamic INSERT statement in AMDP method.

Requirement is , I have to update the cross schema table from another system.

I have to Update Z-Table in BW system .

I created a method in CAR system and run the program to update a table in BW system.

INSERT INTO SAPSD1.ZTABLE select * from :ITAB;

This is updating in BW related Ztable from CAR system . SAPSD1 is BW dev schema. To work this same statement in BW qa and PRD I have written like below.

LV_SQL := 'INSERT INTO' ||:IV_TABLE|| ' select * from :ITAB';

EXEC LV_SQL;

There is no syntactical error in method , but when I execute the program from CAR system I am getting error : SQL Code 257.

NOTE : IV_TABLE. will have the value : SAPSC1.ZTABLE ( when the program execute from CAR DEV)

SAPSQ1.ZTABLE ( When the program execute from CAR QA )

SAPSP1.ZTABLE ( When the program execute from CAR Prod).

Can anyone help me to solve this ?

Re: Dynamic insert statement to update cross schema tables

pfefferf — Thu, 31 Dec 2015 21:26:47 GMT

Hello Latha,

the problem is that the ":ITAB" variable is not accessible within the dynamic sql statement. You can store the values from :ITAB in a local tempoary table which you can use then in the dynamic sql.

But from my point of view I would avoid the dynamic sql and would use a public synonym. The public synonym would point to the table (in the different schemas on the different systems). Within your SQLScript you could code agains the public synoym w/o the dynamic sql part.

Regards, Florian

topic Dynamic insert statement to update cross schema tables in Application Development and Automation Discussions

Dynamic insert statement to update cross schema tables

Re: Dynamic insert statement to update cross schema tables