topic Re: insert only 1 record when use Proc Append to Azure Synapse SQL Pool in Microsoft Integration with SAS

insert only 1 record when use Proc Append to Azure Synapse SQL Pool

osmelbrito — Thu, 01 Sep 2022 18:57:09 GMT

I have a Azure synapse SQL Pool where I have to insert data using SAS

libname bet sqlsvr datasrc="BET" user=datanalitica password="XXXXXX" schema="BET" 
                   USE_ODBC_CL=YES insertbuff=30000
                   insert_sql=yes autocommit=yes stringdates=yes
                   ;

I successfully connect to the database, but when I try to insert data using proc append

proc append base=bet.DIM_RAMO_FINANZAS () data=W_DIM_RAMO_FINANZAS nowarn ;
run;

It only inserts the first record of the dataset even though in log proc append shows that added all records

SQLSRV_4: Prepared: on connection 2
INSERT INTO "BET"."DIM_RAMO_FINANZAS" 
("RAMO_FINANZAS_CD","FILIAL_GRUPO_SK","RAMO_FINANZAS_NM","RAMO_FINANZAS_DESC","TIPO_RAMO_CD","ORDEN_RAMO_ID","PROCESSED_DTTM")  
VALUES ( ? , ? , ? , ? , ? , ? , ? )
 
NOTE: There were 23 observations read from the data set WORK.W_DIM_RAMO_FINANZAS.
NOTE: 23 observations added.
NOTE: The data set BET.DIM_RAMO_FINANZAS has . observations and 7 variables.
 
SQLSRV_5: Executed: on connection 2
Prepared statement SQLSRV_4
 
 
Summary Statistics for SQLSVR are:
Total SQL execution seconds were:                   0.140779
3                                                          The SAS System                          14:22 Thursday, September 1, 2022

Total SQL prepare seconds were:                     0.000061
Total SQL describe seconds were:                    0.049702
Total seconds used by the SQLSVR ACCESS engine were     0.281108

I have SAS 9.4 M6

Re: insert only 1 record when use Proc Append to Azure Synapse SQL Pool

osmelbrito — Thu, 01 Sep 2022 19:04:04 GMT

I just tried using proc sql and insert, and it happens the same, it only insert first record

Re: insert only 1 record when use Proc Append to Azure Synapse SQL Pool

SASKiwi — Thu, 01 Sep 2022 22:38:50 GMT

This looks like a problem best suited to SAS Tech Support. You could try setting DBCOMMIT to 0 to enforce a single commit for the inserts.

libname bet sqlsvr datasrc="BET" user=datanalitica password="XXXXXX" schema="BET" USE_ODBC_CL=YES insertbuff=30000 dbcommit = 0
                   insert_sql=yes autocommit=yes stringdates=yes
                   ;

Re: insert only 1 record when use Proc Append to Azure Synapse SQL Pool

osmelbrito — Fri, 02 Sep 2022 14:12:19 GMT

I just tried different values of inserbuff and dbcommit and it does not work (including recommendation dbcommit=0)

When I used insertbuff=1 it works. But, it is supposed that this parameter increase speed for insertion? if I use 1, massive insertions could be slow?

Re: insert only 1 record when use Proc Append to Azure Synapse SQL Pool

SASKiwi — Sun, 04 Sep 2022 03:16:31 GMT

I think you are correct - using INSERTBUFF = 1 as a workaround isn't likely to be very efficient. As already mentioned raising this with Tech Support is your best option.

Re: insert only 1 record when use Proc Append to Azure Synapse SQL Pool

osmelbrito — Tue, 13 Sep 2022 16:48:44 GMT

I just opened a support track and the solution they found was not use USE_ODBC_CL parameter or setting to NO

after this, with this libname

libname bet sqlsvr datasrc="BET" user=datanalitica password=XXXXXXXXXXXXXXXXXXXX schema="BET" USE_ODBC_CL=NO insertbuff=30000

insertion of full records works perfect

Re: insert only 1 record when use Proc Append to Azure Synapse SQL Pool

Tom — Tue, 13 Sep 2022 18:35:34 GMT

So the explanation of that options is just as clear as mud to me.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p0nhsktrtipxw9n1p8ypfk9vq6fk.htm

But it does tend to make sense that there should not be any need to use "block cursors", whatever the heck that means, for a simple insert.