Temporary Table Support for OLE DB

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

When you want to use temporary tables that persist across SAS procedures and DATA steps with OLE DB, you must use the CONNECTION=SHARED LIBNAME option. In doing so, 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 singe connection.

Using the sample Internat table, this example creates a temporary table, #LONDON, with Microsoft SQL Server. It 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 oledb Provider=SQLOLEDB Password="dbigrp1" UID=dbitest
                      DSN='lupin\sql2000' 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, New, is created with Microsoft SQL Server. The data from this table is then appended to an existing SQL Server table, Inventory.

libname samples oledb provider=SQLOLEDB dsn=lupinss
                      uid=dbitest pwd="dbigrp1";

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 oledb as test (provider=SQLOLEDB dsn=lupinss
                             uid=dbitest pwd=dbigrp1);
   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;