Copy table from SQL Server to Oracle in SAS- Column Names Too Long in SQL Server

1
votes

We are trying to copy a full table from a SQL Server database to Oracle in SAS EGuide while keeping the column names. Unfortunately, some tables in SQL Server have column names longer than the 32 character SAS limit.

For example, the "USR" table in SQL Server has a field "Identity_Source_System_Modified_On" and SAS truncates the column to "Identity_Source_System_Modified_". The column label in SAS maintains the full name.

Is there a way to create this table in Oracle using the column label as opposed to the column name? Or another way to copy this table without SAS truncating the column name?

libname DEST oracle user=&user. pass = &pass. path = 'ORACLE100' 
schema=TESTSCHEMA buffsize = 25000;

proc sql;
connect to odbc (uid="&user." pwd="&passsql." dsn='goven');
create table User_Table as select * from connection to odbc
(
select * from dbo.USR  
);
disconnect from odbc;
quit ;

proc sql;
create table Dest.USR as
select 
*
from work.User_Table;
quit;
2
sqlsas

2 Answers

2
votes

SAS/Access always use SAS underhood, so the truncation will always happen as maximum length for table name is 32. Column labels do not get truncated because maximum length for label is 256( generally label is DBMS table name).

Try to use define sqlserver libname and move the data using proc fedsql. 

 Libname sqlsrv sqlserver details

 proc fedsql;
    create table Dest.USR as
  select 
   *
 from sqlsrv.User_Table;
 quit;

PROC FedSQL can come to rescue, when dealing with longer column length issues. Advantage of PROC FedSQL is that when it connects to data source, it matches metadata of data source and target respectively and translates to source/target metadata without involving SAS/Access, so the truncation does not happen. As oracle(source) and sqlserver(target) both can easily accommodate the length as current version of sql server and oracle accommodate column length of 128.

I had similar issue when I was moving data rom Hive to Teradata and was able to fix using proc fedsql and fulls details in one of the paper I wrote.

https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-41_Final_PDF.pdf

2
votes

If you use foundation SAS as the intermediary for the inter-system copy you can post copy submit pass through that renames the Oracle columns.

I don't see a need to copy the SQL Server table to SAS work prior to copying to Oracle.

After the copy codegen alter table statements that are submitted to Oracle.

Proc SQL;
  * Use SAS as the conduit between the two database systems;

  create table dest.usr as select * from connection to odbc 
  (
    select * from dbo.usr
  );

  * Copy one row from SQL Server to see what the mediated column names were;

  create table one as select * from connection to odbc
  (
    select top 1 * from dbo.usr
  );

  * For the column names that dont match column labels code gen an alter statement;

  reset noprint;

  %let renames=;

  select 'alter table usr rename ' || trim(name) || ' to ' || trim(label) || ';' 
  into :renames
  separated by ' '
  from dictionary.columns
  where libname = 'WORK'
    and memname = 'ONE'
    and name ne label
  ;

  connect using dest;
  execute ( &renames ) by dest;
quit;