Temporary Table Support for ODBC

For general information about this features, see Temporary Table Support for SAS/ACCESS

When you want to use temporary tables that persist across SAS procedures and DATA steps with ODBC, you must use the CONNECTION=SHARED LIBNAME option. When you do this, the temporary table is available for processing until the libref is closed.

You can drop a temporary table at any time, or allow it to be implicitly dropped when the connection is terminated. Temporary tables do not persist beyond the scope of a single connection.

Using the Internat sample table, this example creates a temporary table, #LONDON, with Microsoft SQL Server that contains information about flights that flew to London. This table is then joined with a larger SQL Server table that lists all flights, March, but matched only on flights that flew to London.

libname samples odbc dsn=lupinss uid=dbitest pwd="dbigrp1" connection=shared;

data samples.'#LONDON'n;
   set work.internat;
   where dest='LON';
run;

proc sql;
   select b.flight, b.dates, b.depart, b.orig
          from samples.'#LONDON'n a, samples.march b
          where a.dest=b.dest;
quit;

In this example a temporary table called New is created with Microsoft SQL Server. The data from this table is then appended to an existing SQL Server table named Inventory.

libname samples odbc dsn=lupinss uid=dbitest pwd="dbigrp1" connection=shared;

data samples.inventory(DBTYPE=(itemnum='char(5)' item='varchar(30)'
                       quantity='numeric'));
   itemnum='12001';
      item='screwdriver';
      quantity=15;
      output;
   itemnum='12002';
      item='hammer';
      quantity=25:
      output;
   itemnum='12003';
      item='sledge hammer';
      quantity=10;
      output;
   itemnum='12004';
      item='saw';
      quantity=50;
      output;
   itemnum='12005';
      item='shovel';
      quantity=120;
      output;
run;

data samples.'#new'n(DBTYPE=(itemnum='char(5)' item='varchar(30)'
                     quantity='numeric'));
   itemnum='12006';
      item='snow shovel';
      quantity=5;
      output;
   itemnum='12007';
      item='nails';
      quantity=500;
      output;
run;

proc append base=samples.inventory data=samples.'#new'n;
run;

proc print data=samples.inventory;
run;

This example demonstrates the use of a temporary table using the SQL pass-through facility.

proc sql;
   connect to odbc as test (dsn=lupinss uid=dbitest
                            pwd=dbigrp1 connection=shared);
   execute (create table #FRANCE (flight char(3), dates datetime,
            dest char(3))) by test;

   execute (insert #FRANCE select flight, dates, dest from internat
            where dest like '%FRA%') by test;
   select * from connection to test (select * from #FRANCE);
quit;