Proc Append failing due to IDENTITY_INSERT error for table column that is auto-incremented

[RichM]

Hi,

This proc append

proc append base = warehse.cmmn_data_secmaster data = WORK.securitymaster_trans force;   
run;

Is generating this error

WARNING: Variable SecurityMasterID was not found on DATA file.
WARNING: Variable exchange_country has different lengths on BASE and DATA files (BASE 3 DATA 6).
WARNING: Variable variable_id has different lengths on BASE and DATA files (BASE 30 DATA 13).
WARNING: Variable value has different lengths on BASE and DATA files (BASE 256 DATA 100).
ERROR: CLI execute error: [OpenLink][ODBC][SQL Server]Cannot insert explicit value for identity column in table
        'cmmn_data_secmaster' when IDENTITY_INSERT is set to OFF. (544) : [OpenLink][ODBC][SQL Server]General SQL Server error:
        Check messages from the SQL Server (544) : [OpenLink][ODBC][SQL Server]Attempt to bind to a non-existent column (544)

WORK.securitymaster_trans has these column

identifier

exchange_country

variable_id

value

The table warehse.cmmn_data_secmaster has the same columns but it also has SecurityMasterID which is an auto-incremented integer primary key. There are no additional columns.

Finally, my question

Why is proc append failing due to SecurityMasterID missing in the data set?  Obviously, we would want to the have the database create the PK integer.

Thanks in advance for you help!

[ballardw]

You may have to work with the admin for the SQL server because that variable, SecurityMasterID, sounds likely to be a key, and possibly a unique key, variable and apparently the data base requires its presence on any record in that table. So missing => not added at the SQL server side. I suspect that: when IDENTITY_INSERT is set to OFF is the SQL setting involved that would have to be changed on the base data table in SQL Server but that may not be appropriate if the values of the variable SecurityMasterID are supposed to be assigned/maintained elsewhere in the database.

Also you should notice that exchange_country is going to get truncated as you are attempting to add a 6 character value to a 3 character field.

[RichM]

Hi,

SecurityMasterID is definitely a unique key.  The strange thing is that this proc sql works.

proc sql noprint;

    INSERT INTO warehse.cmmn_data_secmaster (identifier,exchange_country,variable_id,value) VALUES ('rm2','rm2','rm2','rm2');

quit;

I believe that this tells me that the issue is not on the database since the insert correctly results in an auto-incremented value for SecurityMasterID in the database when it is not in the insert.  But I may be wrong.

I also know for sure that the dataset WORK.securitymaster_trans does not have a SecurityMasterID column.  Maybe I need to sort out how to see the IDENTITY_INSERT setting value prior to running the proc append.  The error says it is off but maybe it isn't.  Probably, grasping at straws with that thought.

Thanks,

Rich

[Tom]

PROC APPEND is going to insert a full record.  See if you can create the same error by trying to insert like this.

proc sql noprint;
INSERT INTO warehse.cmmn_data_secmaster (SecurityMasterID ,identifier,exchange_country,variable_id,value) 
VALUES (.,'rm2','rm2','rm2','rm2')
;
quit;

Why not just use the INSERT command instead of the PROC APPEND?  So if your new records are in dataset named HAVE then you  want something like this.

proc sql noprint;
INSERT INTO warehse.cmmn_data_secmaster (identifier,exchange_country,variable_id,value) 
select identifier,exchange_country,variable_id,value 
from have
;

You could even generate that list of column names into a macro variable by querying the metadata for the table and excluding the variable SECURITYMASTER that you don't want to insert into.