topic Two Identical Table one at work lib and another in sql sever in SAS Data Science

Two Identical Table one at work lib and another in sql sever

dhavalyparikh — Fri, 03 May 2024 19:12:40 GMT

Two Identical Table one at work lib and another in sql sever - One getting generated thru SAS process @ Work Library and based on that I would like to simply update the SQL Server table. What is the best way to do so ?

I have 100+ columns in Work lib table and same in SQL server table.

I have library with SQL Server.

Re: Two Identical Table one at work lib and another in sql sever

Reeza — Fri, 03 May 2024 20:33:17 GMT

Upload table to your DB in a temp table.

Use an UPDATE/MODIFY (depends on DB) statement to update your table based on the temp table.

Re: Two Identical Table one at work lib and another in sql sever

Patrick — Sun, 05 May 2024 07:19:30 GMT

I assume identical means "identical in structure" and not "identical data".

If your target is a database like SQL Server then you want to avoid pulling data from the DB into SAS. For this reason you first load your SAS data into the database (ideally a temporary table) and then execute the Update fully in-database (out of SAS: explicit pass-through SQL with an execute() block).

I guess another challenge are these 100+ columns and that you don't want to explicitly type them all in your code. Now that you're on the SQL Server side Google SQL Server forums for solutions to this challenge. If you only need an Update then this discussion might be useful for you.

And just another thought:

There are always these challenges with floating point number precision when exchanging data between platforms (=some very small differences due to how they get stored).

I'm not 100% sure but eventually using bulkload for loading the temp table could get you around this challenge because the data exchange is potentially textual (there are many bl_... options that let you fine tune what's happening).