topic Re: Using a SAS transaction dataset to delete records from SQL Server table in SAS Data Management

Using a SAS transaction dataset to delete records from SQL Server table

Quentin — Fri, 05 Jan 2018 20:07:09 GMT

I have a SQL server master table, and a SAS dataset of transactions which are deletions to be applied.

Thanks to some advice from @ScottBass in https://communities.sas.com/t5/General-SAS-Programming/Can-I-update-a-SQL-Server-table-in-place-using-the-modify/td-p/316149, I've made progress.

I upload my transaction dataset to SQL server, then use explicit pass through to execute the SQL server MERGE statement. My code looks like:

***Setup ;

*SQL master table (sql libref points to sql server database);
data sql.qMaster ;
  do id1=1 to 10 ;
    do id2= 1 to 5 ;
      output ;
    end ;
  end ;
run ;

*SAS transaction dataset of records to delete from the SQL data ;
data qTrans ;
  do id1=3,6,9 ;
    do id2=3,4 ;
      output ;
    end ;
  end ;
run ;


*PROC SQL to upload transaction table to SQL, then execute the  SQL server merge;

proc sql;
  create table sql.qTrans as
    select * from qTrans 
  ;      
 
  connect to ODBC as myconn
    (datasrc=... user=... password="...");
  ;

  execute (
    merge qMaster 
    using qTrans
    on (qMaster.id1=qTrans.id1 and qMaster.id2=qTrans.id2)
    when matched then delete 
    ;
  ) by myconn ;

  disconnect from myconn ;
quit ;

It looks like that is doing what I want. I feel like when I upload qTrans to SQL server I should make it a temporary table, but I couldn't get it to work. I tried e.g. create table sql.'#qTrans'n but couldn't get it to work. And I don't know anything about SQL server temp tables, would #qTrans persist long enough to be seen by my execute statement? What about if I made it ##qTrans? I suppose I don't have much to gain by making it an official temporary table rather than just dropping qTemp at the end, but figured it's worth a shot

Any other recommendations for improvements to this approach?.

Re: Using a SAS transaction dataset to delete records from SQL Server table

LinusH — Fri, 05 Jan 2018 20:16:05 GMT

Have you read this?
http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#p1h2yn2en7gkb8n1mba66mxgnf56.htm

Re: Using a SAS transaction dataset to delete records from SQL Server table

kiranv_ — Fri, 05 Jan 2018 20:44:46 GMT

Adding to @LinusH both temporary tables are for a session. only difference # and ## is , ## tables are global temp tables, which are available to all the users where #tables are local ones, which are available only to the user. There is no performance enhancement for either.

of the topic, huge performance impacts are often seen when data is moved from temporary dbms to SAS as opposed to data movement from Permanent dbms table to SAS. to conclude, Moving a volatile/temporary DBMS table to SAS is very slow.

Re: Using a SAS transaction dataset to delete records from SQL Server table

Quentin — Sat, 06 Jan 2018 22:19:36 GMT

@kiranv_ can you explain your second paragraph? I can't understand it. Are you saying that the process of using SAS/ACCESS to read from a SQL server temporary table is faster/slower than reading from a SQL server permanent table?

Thanks for the explanation of # vs ##. That helps.

Re: Using a SAS transaction dataset to delete records from SQL Server table

kiranv_ — Sat, 06 Jan 2018 22:34:49 GMT

Sorry for being unclear.

Say you want to create a SAS table from SQL server table and your SQL server table is temporary one, then it is slow. Instead of temporary table if you create a SAS table from SQL a permanent table, it is often much faster. This property I have observed many times both in SQL server and Teradata, especially at my last client location.

Re: Using a SAS transaction dataset to delete records from SQL Server table

Quentin — Sun, 07 Jan 2018 00:21:55 GMT

Thanks @LinusH and @kiranv_. I hadn't seen that page of the docs. Looks like adding connection=shared to the libname statement was the key. And I had to use ##tableNames to get it to work. I suspect it's because I use implicit pass through to write the SAS transaction table to SQL server, then use explicit pass through to execute the MERGE statement. So I suppose to SQL, I may be two different sessions.

Looks like below works:

libname sql odbc dsn= user="..." password="..." schema=dbo connection=shared;

data sql.qMaster (insertbuff=1000);
  do id1=1 to 10 ;
    do id2= 1 to 5 ;
      output ;
    end ;
  end ;
run ;


*SAS dataset of records to delete from the SQL data ;
data qTrans ;
  do id1=3,6,9 ;
    do id2=3,4 ;
      output ;
    end ;
  end ;
run ;


proc sql;

  *upload work.qTrans to SQL server as temporary table ;
  create table sql.'##qTrans'n (insertbuff=1000) as
    select * from qTrans 
  ;      
 
  connect to ODBC as myconn
    (datasrc="" user= password="");
  ;

  execute (
    merge qMaster 
    using ##qTrans
    on (qMaster.id1=##qTrans.id1 and qMaster.id2=##qTrans.id2)
    when matched then delete 
    ;
    drop table ##qTrans  ;
  ) by myconn ;

  disconnect from myconn ;

  create table want as 
  select * from sql.qMaster 
  ;
quit ;

libname sql clear ;

Still happy to hear any suggested improvements.

Re: Using a SAS transaction dataset to delete records from SQL Server table

ScottBass — Mon, 15 Jan 2018 02:16:07 GMT

I was in the midst of replying to the OP by @Quentin when the solution(s) were posted.

However, since I put some work into this, here goes, apologies if some of this is redundant...

First of all, have a look at https://www.red-gate.com/simple-talk/sql/t-sql-programming/temporary-tables-in-sql-server/. It's a bit long, but goes into details about temp tables and table variables.

Second, do this in SQL Server Management Studio:

1) In query window #1:

SELECT name, database_id INTO tempdb.dbo.table1 FROM master.sys.databases
SELECT name, database_id INTO #table2 FROM master.sys.databases 
SELECT name, database_id INTO ##table3 FROM master.sys.databases

SELECT * FROM tempdb.INFORMATION_SCHEMA.Tables WHERE table_name LIKE '%table%'

SELECT * FROM tempdb.dbo.table1
SELECT * FROM #table2
SELECT * FROM ##table3

2) In a second query window:

SELECT * FROM tempdb.INFORMATION_SCHEMA.Tables WHERE table_name LIKE '%table%'

SELECT * FROM tempdb.dbo.table1
SELECT * FROM ##table3 
SELECT * FROM #table2

In the 2nd query window, you can see that table1, #table2, and ##table3 exist, but you can only access table1 and ##table3; #table2 can only be accessed by the process ID that created it. (You can submit SELECT @@spid to get the process ID, but it's also displayed next to your userid in SSMS).

Now, close the first query window, and resubmit the code in the second query window. You'll see that #table2 and ##table3 have disappeared. So, the Temporary Tables persist only for the life of the process ID that created them, and global temporary tables can be accessed by other process ID's, but again *they only exist during the life of the process ID that created them*.

With that in mind, submit the below code in SAS EG (modify as required).

* set desired SAS options ;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options msglevel=I;
options fullstimer;
options mprint mrecall;
options compress=binary;
options ls=max;
options nocenter;
options lognumberformat=1;

libname TMP odbc NOPROMPT="Driver={SQL Server Native Client 10.0};Server=XXXXXXXX;Database=TempDB;Trusted_Connection=yes;" bulkload=yes schema=dbo ;

* #1A ;
proc sql;
   connect using tmp;
   execute by tmp (
      SELECT name, database_id INTO tempdb.dbo.table1 FROM master.sys.databases;
   );
   select * from connection to tmp (
      SELECT * FROM tempdb.dbo.table1;
   );
   execute by tmp (
      DROP TABLE tempdb.dbo.table1;
   );
quit;

* #1B ;
proc sql;
   connect using tmp;
   execute by tmp (
      SELECT name, database_id INTO tempdb.dbo.table1 FROM master.sys.databases;
   );
quit;

proc sql;
   connect using tmp;
   select * from connection to tmp (
      SELECT * FROM tempdb.dbo.table1;
   );
   execute by tmp (
      DROP TABLE tempdb.dbo.table1;
   );
quit;

* #2A ;
proc sql;
   connect using tmp;
   execute by tmp (
      SELECT name, database_id INTO #table2 FROM master.sys.databases;
   );
   select * from connection to tmp (
      SELECT * FROM #table2;
   );
   execute by tmp (
      DROP TABLE #table2;
   );
quit;

* #2B ;
proc sql;
   connect using tmp;
   execute by tmp (
      SELECT name, database_id INTO #table2 FROM master.sys.databases;
   );
quit;

proc sql;
   connect using tmp;
   select * from connection to tmp (
      SELECT * FROM #table2;
   );
   execute by tmp (
      DROP TABLE #table2;
   );
quit;

* #3A ;
proc sql;
   connect using tmp;
   execute by tmp (
      SELECT name, database_id INTO ##table3 FROM master.sys.databases;
   );
   select * from connection to tmp (
      SELECT * FROM ##table3;
   );
   execute by tmp (
      DROP TABLE ##table3;
   );
quit;

* #3B ;
proc sql;
   connect using tmp;
   execute by tmp (
      SELECT name, database_id INTO ##table3 FROM master.sys.databases;
   );
quit;

proc sql;
   connect using tmp;
   select * from connection to tmp (
      SELECT * FROM ##table3;
   );
   execute by tmp (
      DROP TABLE ##table3;
   );
quit;

* #4 ;
proc sql;
   connect using tmp;

   create table tmp.'#class2'n as
   select * from sashelp.class;

   select * from connection to tmp (
      SELECT * FROM #class2;
   );
   execute by tmp (
      DROP TABLE #class2;
   );
quit;

* #5 ;
proc sql;
   connect using tmp;

   create table tmp.'##class3'n as
   select * from sashelp.class;

   select * from connection to tmp (
      SELECT * FROM ##class3;
   );
   execute by tmp (
      DROP TABLE ##class3;
   );
quit;

My conclusion is that the connections from SAS to SQL Server are transient, with each connection resulting in a new process ID. This is then problematic for the reasons above. Furthermore, global temporary tables can have security issues if the data is sensitive.

After pulling out my hair a few times trying to make temporary tables "work", my usual approach has been to create a "tmp" schema in my database, writing "temporary" tables to the tmp schema, and dropping the tables once I'm done with them.

Hope this helps...

Re: Using a SAS transaction dataset to delete records from SQL Server table

Quentin — Mon, 15 Jan 2018 14:19:08 GMT

Thanks @ScottBass, that helps a lot in understanding how SQL server temp tables work.

And as so often happens in forums like this, in addressing my main question you showed me something unrelated which will be a huge benefit to me. I haven't seen the connect USING <libref> syntax before. That is a lovely thing! Apparently introduced in 9.3, so I'm only about six years late to the party... : ) https://philihp.com/blog/2012/reusing-a-libnames-database-connection-in-pass-through/