10-13-2017 02:29 PM
I am trying to use a SAS DI table loader transformation to load into a SQL Server table that is using a sequence value as a default constraint for the PK column. The insert works outside of SAS DI if exclude the PK column from the insert statement and let the default constraint populate the PK column value.
In SAS DI even though the PK column is not mapped in the Table Loader mapping the transformation is still generating a insert SQL statement that includes the PK column and a null value which results in the error below:
ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'XXXXXXXXXXXXXX_ID', table 'xxxxxxxxxxx'; column does not allow nulls. INSERT fails. : [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated.
I have tried the solution to add the IGNORE_READ_ONLY_COLUMNS=YES to the advanced option of the library definition but I still get the error.
Any ideas on how to accomplish the insert and have the PK column ignored in the insert?
10-13-2017 05:39 PM
10-13-2017 05:39 PM
10-14-2017 06:53 AM - edited 10-14-2017 06:54 AM
10-14-2017 06:24 PM
Thank you Linus and Patrick for your responses.
Based on your responses and a previous post by "nar_sas" I believe the following are viable solutions:
1. Set the ignore_read_only_columns=yes option on either the library or table definition will prevent the column from being included in the Insert statement BUT this only works if the column is an IDENTITY (i.e. read-only).
This allows the metadata for the table definition to remain complete and the identity column will be auto incremented as expected.
2. Add a "drop=<ColumnName>" statement into the options on the table load transformation (Options -> Loader* -> Additional data table options) and again the column specific will be excluded from the insert statement.
This allows the metadata for the table definition to remain complete. In addition this works for both identity columns and those using a sequence and default constraint (i.e. not read-only). Both will be auto incremented as expected. This appears to work for both the Insert (SQL) and Append (Proc Append) News Rows options in the load technique settings.
3. Alter the metadata for the target table to not include the columns to be excluded.
I haven't tested this one yet but I suspect it will get around the issue of the unmapped columns being included in the insert but comes with the side affect of not providing a accurate representation of the table.
10-14-2017 06:31 PM
Need further help from the community? Please ask a new question.