Specifies whether to ignore or include columns where data types are read-only when generating an SQL statement for inserts or updates.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Alias: | IGNORE_READONLY= [Greenplum, HAWQ, SAP IQ] |
| Default: | NO |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Greenplum, HAWQ, Impala, Microsoft SQL Server, Netezza, ODBC, OLE DB, PostgreSQL, SAP HANA, SAP IQ, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | IGNORE_READ_ONLY_COLUMNS= data set option |
Table of Contents
specifies that the SAS/ACCESS engine ignores columns where data types are read-only when you are generating insert and update SQL statements.
specifies that the SAS/ACCESS engine does not ignore columns where data types are read-only when you are generating insert and update SQL statements.
Several databases include data types that can be read-only, such as the data type of the Microsoft SQL Server timestamp. Several databases also have properties that allow certain data types to be read-only, such as the Microsoft SQL Server identity property.
When IGNORE_READ_ONLY_COLUMNS=NO and a DBMS table contains a column that is read-only, an error is returned indicating that the data could not be modified for that column.
For this example, a database that contains the table Products is created with two columns: ID and PRODUCT_NAME. The ID column is specified as a Read-only data type and PRODUCT_NAME is a character column.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product_name varchar(40))Assume you have a SAS data set that contains the name of your products, and you would like to insert the data into the Products table.
data work.products;
id=1;
product_name='screwdriver';
output;
id=2;
product_name='hammer';
output;
id=3;
product_name='saw';
output;
id=4;
product_name='shovel';
output;
run;With IGNORE_READ_ONLY_COLUMNS=NO (the default), an error is returned by the database because in this example the ID column cannot be updated. However, if you set the option to YES and execute a PROC APPEND, the append succeeds, and the SQL statement that is generated does not contain the ID column.
libname x odbc uid=myusr1 pwd=myusr1 dsn=lupinss
ignore_read_only_columns=yes;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc append base=x.PRODUCTS data=work.products;
run;