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
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
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
;
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?
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.
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!