Dynamic insert statement to update cross schema tables

[Former Member]

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 ?

[Former Member]

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 ?

[pfefferf]

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