0 Replies Latest reply: Jun 10, 2016 11:59 AM by Wesley Kalata RSS

    Dynamic SQL Generation to Load Data from Multiple Sources

    Wesley Kalata

      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