The following code is what I have thus far:
SET vDataSource_ServerName='';
SET vDataSource_DatabaseName='';
SET vDataSource_SchemaName='';
SET vDataSource_DIR='';
SET vSQL=' ';
SET vIdx=1;
For vIdx = 1 to NoOfRows('REF_DATA_SOURCES')
Let vDataSource_ServerName =Peek('ServerName',vIdx,'REF_DATA_SOURCES');
Let vDataSource_DatabaseName=Peek('DatabaseName',vIdx,'REF_DATA_SOURCES');
Let vDataSource_SchemaName =Peek('SchemaName',vIdx,'REF_DATA_SOURCES');
Let vDataSource_DIR =Peek('DIR',vIdx,'REF_DATA_SOURCES');
//LET vSQL = $(vSQL) & 'SELECT * from ' & $(vDataSource_ServerName) & '.' & $(vDataSource_DatabaseName) & '.' & $(vDataSource_SchemaName) & '.DiagCdMst with (nolock)';
SET vSQL = '
select distinct
''DP_' & $(vDataSource_DIR) & ''' [SourceSystem]
, ''POSCd'' [CodeType]
, LTRIM(RTRIM([POSCd])) [CodeName]
, LTRIM(RTRIM([PlcOfSrvcDesc])) [CodeDesc]
from ' & $(vDataSource_ServerName) & '.' & $(vDataSource_DatabaseName) & '.' & $(vDataSource_SchemaName) & '.PlcOfSrvcCdMst [poscm] with (nolock)';
POSCdMst:
LOAD
AutoNumber( [SourceSystem] & '|' &
[CodeType] & '|' &
[CodeName] & '|' &
[CodeDesc]
) as [ID]
, [SourceSystem]
, [CodeType]
, [CodeName]
, [CodeDesc]
;
SQL $(vSQL);
Next vIdx
I am trying to pull data in from multiple databases, and I have the server name, database name, and schema name available. Can this be done?
[Update 2016-06-10 1158 EST]
I changed the SET to a LET and it worked!
LET vSQL = '
select distinct
''DP_$(vDataSource_DIR)'' [SourceSystem]
, ''POSCd'' [CodeType]
, LTRIM(RTRIM([POSCd])) [CodeName]
, LTRIM(RTRIM([PlcOfSrvcDesc])) [CodeDesc]
from $(vDataSource_ServerName).$(vDataSource_DatabaseName).$(vDataSource_SchemaName).PlcOfSrvcCdMst [poscm] with (nolock)
order by LTRIM(RTRIM([POSCd]))
';
Message was edited by: Wesley Kalata
