Repeat the same SQL queries for multiple ODBC connections

[#1]

Hi everyone.

I want to find an efficient way to repeat the same LOAD statements and SQL queries for multiple ODBC connections. My current script looks like this:

LIB CONNECT TO 'DB1';

LOAD ... ;

SQL ...;

LIB CONNECT TO 'DB2';

LOAD ... ;

SQL ...;

LIB CONNECT TO 'DB3';

LOAD ... ;

SQL ...;

LIB CONNECT TO 'DB4';

LOAD ... ;

SQL ...;

Is there a better way to do this? Because the SQL queries are quite long, can we avoid repeating the same

"

LOAD ... ;

SQL ...;

"?

[adamdavi3s]

Can you just pop them in an include script and call that each time?

That is what I would do but there might be a better way.

[maxgro]

you can set 2 variables for load and sql

SET vLoad = Load FIELD1, FIELD2;

SET vSQL = SQL SELECT

FIELD1, FIELD2

FROM TABLE1

where ..........;

LIB CONNECT TO 'DB1';

$(vLoad);

$(vSQL);

LIB CONNECT TO 'DB2';

$(vLoad);

$(vSQL);

or maybe also in a for loop on your DB1...2

[Anonymous]

Maybe a loop to keep on changing the connection like below?

>> List your connections here or load from excel file
DBConnList:
LOAD * INLINE [
    DBName
    DB1
    DB2
    DB3
    DB4
];

LET vRowCount = NoOfRows('DBConnList');

for a = 1 to $(vRowCount)

let vValueToRead = FieldValue( 'datafiles', $(a));

LIB CONNECT TO $(vValueToRead)

LOAD ... ;

SQL ...;

NEXT

[Peter_Cammaert · Accepted solution]

Or maybe even simpler with a FOR EACH

FOR EACH vConnectionString in 'String1', 'String2', 'String3'

  CONNECT TO [$(vConnectionString)];

  Table:

  LOAD * FROM Table; // Will perform an AUTOCONCATENATE if tables are identical

  DISCONNECT;

NEXT

Best,

Peter

[#6]

Thank you all for your ideas.

All answers are awesome!