topic How to use a SAS DI Table Loader to insert into table and ignore un-mapped target columns in SAS Data Management

How to use a SAS DI Table Loader to insert into table and ignore un-mapped target columns

TD2017 — Fri, 13 Oct 2017 18:29:58 GMT

Hello

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?

Thanks

Re: How to use a SAS DI Table Loader to insert into table and ignore un-mapped target columns

LinusH — Fri, 13 Oct 2017 21:39:13 GMT

If I recall correctly you can't have that column in your table meta data definition.

Re: How to use a SAS DI Table Loader to insert into table and ignore un-mapped target columns

Patrick — Sat, 14 Oct 2017 10:54:01 GMT

@TD2017

Is the column explicitly defined as readonly in the SQL Server table? Else: What @LinusH says.

Re: How to use a SAS DI Table Loader to insert into table and ignore un-mapped target columns

TD2017 — Sat, 14 Oct 2017 22:24:35 GMT

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.

Thanks again!

Re: How to use a SAS DI Table Loader to insert into table and ignore un-mapped target columns

TD2017 — Sat, 14 Oct 2017 22:31:59 GMT

It appears there is no way to mark the answers from both @Patrick and @LinusH as solutions but the credit should go to both as well as @nar_sas from an older post.

Thanks all!

Re: How to use a SAS DI Table Loader to insert into table and ignore un-mapped target columns

Patrick — Sun, 15 Oct 2017 00:48:38 GMT

@TD2017

Thanks for taking the time to share the results of your testing.