IGNORE_READ_ONLY_COLUMNS= Data Set Option

Specifies whether to ignore or include columns whose data types are read-only when generating an SQL statement for inserts or updates.

Valid in:	DATA and PROC steps
Default:	NO
Supports:	All

Syntax

Details

Example

Syntax

IGNORE_READ_ONLY_COLUMNS=YES | NO

Syntax Description

YES: specifies to ignore columns whose data types are read-only when generating INSERT and UPDATE statements

NO: specifies to include columns whose data types are read-only when generating INSERT and UPDATE statements

Details

Several databases include data types that can be read-only, such as the DB2 TIMESTAMP data type. Also, some databases have properties that allow certain data types to be read-only, such as the Microsoft SQL Server identity property.

When the IGNORE_READ_ONLY_COLUMNS= option is set to NO (the default), and a table contains a column that is read-only, an error is returned indicating that the data could not be modified for that column.

Example: Insert Data into a Table

For the following example, a database that contains the table Products is created with two columns: ID and PRODUCT_NAME. The ID column is defined by a read-only data type and PRODUCT_NAME is a character column.

create table x.products (id int identity primary key, product_name varchar(40))

If you have a SAS data set that contains the name of your products, you can insert the data from the SAS data set into the Products table:

data x.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 fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=db2dsn dsnuser=db2user dsnpwd=db2pwd
   ignore_read_only_columns=yes;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc append base=x.productsdata=work.products;
run;