insert into location script in SAP data service

[former_member695592]

I'd like use insert into location in my script. But I get some syntax errors.

What are wrongs below script?

sql('IQ_DATASTORE', 'insert into finance.Selcuk_agent_table LOCATION 'StandByDB.mydb' {select * from mydb.dbo.agent_table}');

I think it results from ' and { characters. How to cope these?

[Julian_Riegel]

Hi there,

try something like:

sql('IQ_DATASTORE', 'insert into finance.Selcuk_agent_table LOCATION \'StandByDB.mydb\' (select * from mydb.dbo.agent_table)');

[former_member695592]

No, it wasn't worked.

It threw that error:

Column <mydb> was not found in table <StandByDB>. Check the spelling of the referenced table and column and ensure that the table exists and such a column exists in the table.

[Julian_Riegel]

Okay, at least the syntax errors are gone now - have you tried executing that sql command in the db client first?

Is it working fine there?

Worst case would be that you need to create a datastore with the second system and then using a standard Query.

Source Table (Datastore1) -> Query -> Target Table(Datastore2)

[former_member695592]

I can execute SQL command client side.

By the way I change my method. I create a proc, input SQL query in the proc. But it didn't work.It threw same error that I determined above.

sql('IQ_DATASTORE', 'execute sp_name');

[former_member695592]

Thank you so much all. I solved the problem. I created stored proc and put script in stored proc. But the problem was going on. I deleted job and create new one, I made same things now it is working.

So, solution is drop-create job 🙂