Multi Server Join using SAS/Access ODBC

It is always best to do most of the processing on a server and only return the filtered results back to the requesting client. In this case I need two columns (state and county) otherwise I might have simply created a macro variable if only a single column were required.

The LIBNAME statement is used to bulk copy (BCP) 8 rows from DEV into the global (##) temp table. Those 8 rows are then joined to an 8 million row table and the entire process takes less than one second.

/* store data into a global (##) temporary table on SQL Server */
libname sqltemp 
  odbc 
  noprompt   = "Driver={SQL Server};Server=devserver;Trusted_Connection=Yes;" 
  bcp        = yes   
  connection = global ;

proc sql ;
  connect to odbc as prod 
    ( "Driver={SQL Server};Server=prodserver;Trusted_Connection=Yes;" ) ;

    /* populate data from prod into a dev SQL Server global temporary table */
    create table sqltemp.'##counties'n as
      select           *
        from           connection to prod
        ( select       *
            from       db.schema.table
            where          statecode  = &state. 
                       and countycode = &county. ) ;
  disconnect from prod ;

  connect to odbc as dev 
    ( "Driver={SQL Server};Server=devserver;Trusted_Connection=Yes;"  ) ;

    create table work.results as
      select            *
        from   connection to dev
        ( select         a.*
            from         db.schema.table a
            inner join   ##counties b        /* SQL Server temp table */
              on             a.state  = b.state
                         and a.county = b.county ) ;
  disconnect from dev ;
quit ;

libname sqltemp clear ;