Need to pass two data source name in SQL query in BODS script

[Former Member]

Hi Team,

I have a requirement in moving data from one source to another source. My source is Oracle view and destination is SQL table. I need to insert the record from source to destination using script in BODS not query transform.

I have written code below

sql("sql datastore name", "insert into sqltablename (col1,col2) select col1,col2 from oracledatastorename.schemaname.tablename");

I am getting error "oracledatastorename.schemaname.tablename doesn't exist".

could you please help me to resolve this issue or is there any other technique we can use in BODS but i don't want to use Query transform.

Thanks,

Sundaram

[former_member187605]

I really do not understand why you would not want to use a query transform for this basic operation. Anyway, if you don't want to, you don't need it. Here is the most of simple of datflows that will suit your purpose well:

[Former Member]

Ok, so that means without query transform also if i join my source and destination table it will move data. one more doubt if my table structure is different, i mean if in my source table 5 columns and in my destination table 8 columns, then this will work?

anyways thanks for this valuable solution.

[former_member187605]

Sure, as long as the columns in your target table form a superset of the ones in your source table, that will work. Just make sure that the remaining target table columns are nullable.

[Former Member]

Hello

For this technique to work you must create a SQL Server linked server (to Oracle).  The error is nothing to do with Data Services,when you use the SQL function,you delegate all control to the DB.

If you used a query and 'db links' in your datastores it would achieve exactly what you require without hand coding the SQL.

Michael

[Former Member]

Thanks MIchael for suggestion.