sql - Cannot insert the value null error but value isn't actually null -
i have error seems simple resolve, have not been able so. when run statement insert 1 record table receive following error:
cannot insert value null column 'isfuturedateallowfordatasubmission', table 'tablea'; column not allow nulls. insert fails. simple, right? trying insert null column doesn't allow it. in insert statement i explicitly inserting value column. nothing fancy, no dynamic sql, no subqueries or that. have simple statement every value enumerated. failing above error
tablea consists of 81 columns. configuration table large web application. column not allow nulls. column data type bit (a true/false flag application). cannot alter design of table.
isfuturedateallowfordatasubmission definition:
+------------------------------------+-----------+-----------+-----------+--------+-------+-------+----------+---------+------------+---------------+------------------+-------------------+------------------+-------------+--------------+ | column_name | data_type | type_name | precision | length | scale | radix | nullable | remarks | column_def | sql_data_type | sql_datetime_sub | char_octet_length | ordinal_position | is_nullable | ss_data_type | +------------------------------------+-----------+-----------+-----------+--------+-------+-------+----------+---------+------------+---------------+------------------+-------------------+------------------+-------------+--------------+ | isfuturedateallowfordatasubmission | -7 | bit | 1 | 1 | null | null | 0 | null | ((0)) | -7 | null | null | 63 | no | 50 | +------------------------------------+-----------+-----------+-----------+--------+-------+-------+----------+---------+------------+---------------+------------------+-------------------+------------------+-------------+--------------+ i passing in 0 isfuturedateallowfordatasubmission, tried '0'. both result in same error.
i haven't found online far other people trying add values in columns set identity, scenario not relevant here column trying add value simple 0/1 flag, not identity.
i happy provide more information if required. actual insert statement long (81 columns explicitly assigning values to).
update
here table (tablea) definition:
+-------------------------------------------------+----------+----------+--------+-------+-------+----------+--------------------+----------------------+------------------------------+ | column_name | type | computed | length | prec | scale | nullable | trimtrailingblanks | fixedlennullinsource | collation | +-------------------------------------------------+----------+----------+--------+-------+-------+----------+--------------------+----------------------+------------------------------+ | busns_unit_id | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | busns_unit_nm | nvarchar | no | 40 | | | yes | (n/a) | (n/a) | sql_latin1_general_cp1_ci_as | | allow_rpg_edit_flag | bit | no | 1 | | | yes | (n/a) | (n/a) | null | | fiscal_yr_start_mth | tinyint | no | 1 | 3 | 0 | no | (n/a) | (n/a) | null | | input_form_excel_flag | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | show_composite_calculation_type | bit | no | 1 | | | yes | (n/a) | (n/a) | null | | default_composite_calculation_type | nvarchar | no | 200 | | | yes | (n/a) | (n/a) | sql_latin1_general_cp1_ci_as | | map_all_dept_flag | bit | no | 1 | | | yes | (n/a) | (n/a) | null | | compst_min_dlrs_to_run | int | no | 4 | 10 | 0 | yes | (n/a) | (n/a) | null | | acct_data_typ | varchar | no | 10 | | | yes | no | yes | sql_latin1_general_cp1_ci_as | | decimal_separator_default | varchar | no | 10 | | | yes | no | yes | sql_latin1_general_cp1_ci_as | | form_readonly_flag | bit | no | 1 | | | yes | (n/a) | (n/a) | null | | cmpst_run_mths | int | no | 4 | 10 | 0 | yes | (n/a) | (n/a) | null | | cmpst_run_yrs | int | no | 4 | 10 | 0 | yes | (n/a) | (n/a) | null | | display_lcl_acct_nbr | bit | no | 1 | | | yes | (n/a) | (n/a) | null | | csv_delimiter | varchar | no | 2 | | | no | no | no | sql_latin1_general_cp1_ci_as | | show_plans | bit | no | 1 | | | no | (n/a) | (n/a) | null | | input_form_template_name | nvarchar | no | 300 | | | yes | (n/a) | (n/a) | sql_latin1_general_cp1_ci_as | | compst_run_reviewstatus_default | tinyint | no | 1 | 3 | 0 | yes | (n/a) | (n/a) | null | | allowduplicateaccountdata | bit | no | 1 | | | no | (n/a) | (n/a) | null | | file_import_schema | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | allow_delete_composite | bit | no | 1 | | | no | (n/a) | (n/a) | null | | disable_multiple_starmap | bit | no | 1 | | | no | (n/a) | (n/a) | null | | allowmasteraccountmap | bit | no | 1 | | | no | (n/a) | (n/a) | null | | showcompositestovendors | bit | no | 1 | | | no | (n/a) | (n/a) | null | | allowcompositeclosedsnapshot | bit | no | 1 | | | no | (n/a) | (n/a) | null | | submissionfrequencyid | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | autorecalculate | bit | no | 1 | | | no | (n/a) | (n/a) | null | | showdealercodealternate | bit | no | 1 | | | no | (n/a) | (n/a) | null | | showsubdealerindicator | bit | no | 1 | | | no | (n/a) | (n/a) | null | | showdealertype | bit | no | 1 | | | no | (n/a) | (n/a) | null | | onlinebusinessplan | bit | no | 1 | | | no | (n/a) | (n/a) | null | | showinactiveitemsadvancedcriteria | bit | no | 1 | | | no | (n/a) | (n/a) | null | | honorrolltype | nvarchar | no | 200 | | | no | (n/a) | (n/a) | sql_latin1_general_cp1_ci_as | | showinactivedealersdatasubmission | bit | no | 1 | | | no | (n/a) | (n/a) | null | | allowfinancialdatasuppression | bit | no | 1 | | | no | (n/a) | (n/a) | null | | allowdatasubmissioncomments | bit | no | 1 | | | no | (n/a) | (n/a) | null | | rejectmatchingsubmissions | bit | no | 1 | | | no | (n/a) | (n/a) | null | | honorrollaccuracypoint | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | honorrollnonsubmissionpoint | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | disableinputformaccounthighlight | bit | no | 1 | | | no | (n/a) | (n/a) | null | | disableinputformauditcomments | bit | no | 1 | | | no | (n/a) | (n/a) | null | | enablesubdealers | bit | no | 1 | | | no | (n/a) | (n/a) | null | | showcompositesindealerprofile | bit | no | 1 | | | no | (n/a) | (n/a) | null | | enablecarryforwardauditremark | bit | no | 1 | | | no | (n/a) | (n/a) | null | | enableinputforminlineevents | bit | no | 1 | | | no | (n/a) | (n/a) | null | | preventovermap | bit | no | 1 | | | no | (n/a) | (n/a) | null | | defaultadhocreportvariablestab | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | workingcapitalformula | nvarchar | no | -1 | | | no | (n/a) | (n/a) | sql_latin1_general_cp1_ci_as | | investmentguideformula | nvarchar | no | -1 | | | no | (n/a) | (n/a) | sql_latin1_general_cp1_ci_as | | dualdealerappointmentmonthcutoff | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | workingcapitalsubmissionsstartmonth | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | workingcapitalsubmissionsendmonth | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | workingcapitalsuccessfulsubmissionsminimum | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | workingcapitalstartmonth | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | workingcapitalendmonth | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | workingcapitalactivationmonthcutoff | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | adhocshowalluserreports | bit | no | 1 | | | no | (n/a) | (n/a) | null | | auditresultslinkstatus | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | showhistoriccompositesentitysearch | bit | no | 1 | | | no | (n/a) | (n/a) | null | | worksheetfullscreenmode | bit | no | 1 | | | no | (n/a) | (n/a) | null | | onlinebusinessforecast | bit | no | 1 | | | no | (n/a) | (n/a) | null | | isfuturedateallowfordatasubmission | bit | no | 1 | | | no | (n/a) | (n/a) | null | | specialcarryforward | bit | no | 1 | | | no | (n/a) | (n/a) | null | | dmsfiletransferformat | nvarchar | no | -1 | | | yes | (n/a) | (n/a) | sql_latin1_general_cp1_ci_as | | sitelogo | varchar | no | -1 | | | yes | no | yes | sql_latin1_general_cp1_ci_as | | submissionpointsthreshold | int | no | 4 | 10 | 0 | no | (n/a) | (n/a) | null | | enablecompositeaveragebydepartment | bit | no | 1 | | | no | (n/a) | (n/a) | null | | viewprevioussubmission | bit | no | 1 | | | no | (n/a) | (n/a) | null | | hideauditresultzerovalues | bit | no | 1 | | | no | (n/a) | (n/a) | null | | keepreleasedfileimportdata | bit | no | 1 | | | no | (n/a) | (n/a) | null | | arlaccountnumberparse | bit | no | 1 | | | no | (n/a) | (n/a) | null | | convertforecastroles | nvarchar | no | -1 | | | yes | (n/a) | (n/a) | sql_latin1_general_cp1_ci_as | | arlenableundo | bit | no | 1 | | | no | (n/a) | (n/a) | null | | unhideauditresultsummaryaccountzerovalues | bit | no | 1 | | | no | (n/a) | (n/a) | null | | alwaysshowauditresultsummaryaccountcarryforward | bit | no | 1 | | | no | (n/a) | (n/a) | null | | compositeaveragesummaryaccounts | bit | no | 1 | | | no | (n/a) | (n/a) | null | | dynamicportfolio | bit | no | 1 | | | no | (n/a) | (n/a) | null | | enableallocationaccounts | bit | no | 1 | | | no | (n/a) | (n/a) | null | | enabletermsandconditions | bit | no | 1 | | | no | (n/a) | (n/a) | null | | enablesimultaneousaudit | bit | no | 1 | | | no | (n/a) | (n/a) | null | +-------------------------------------------------+----------+----------+--------+-------+-------+----------+--------------------+----------------------+------------------------------+ and here full insert statement generates error:
insert tablea select '14' busns_unit_id, 'global' busns_unit_nm, '0' allow_rpg_edit_flag, '1' fiscal_yr_start_mth, '1' input_form_excel_flag, '0' show_composite_calculation_type, 'annualization' default_composite_calculation_type, '0' map_all_dept_flag, '3' compst_min_dlrs_to_run, 'cumulative' acct_data_typ, 'period' decimal_separator_default, '0' form_readonly_flag, '25' cmpst_run_mths, '3' cmpst_run_yrs, '1' display_lcl_acct_nbr, ',' csv_delimiter, '0' show_plans, null input_form_template_name, '4' compst_run_reviewstatus_default, '0' allowduplicateaccountdata, '0' file_import_schema, '1' allow_delete_composite, '0' disable_multiple_starmap, '0' allowmasteraccountmap, '1' showcompositestovendors, '0' allowcompositeclosedsnapshot, '1' submissionfrequencyid, '0' autorecalculate, '0' showdealercodealternate, '0' showsubdealerindicator, '0' showdealertype, '0' onlinebusinessplan, '1' showinactiveitemsadvancedcriteria, 'submission' honorrolltype, '0' showinactivedealersdatasubmission, '0' allowfinancialdatasuppression, '1' allowdatasubmissioncomments, '1' rejectmatchingsubmissions, '0' honorrollaccuracypoint, '0' honorrollnonsubmissionpoint, '0' disableinputformaccounthighlight, '0' disableinputformauditcomments, '0' enablesubdealers, '1' showcompositesindealerprofile, '0' enablecarryforwardauditremark, '0' enableinputforminlineevents, '0' preventovermap, '0' defaultadhocreportvariablestab, '' workingcapitalformula, '' investmentguideformula, '0' dualdealerappointmentmonthcutoff, '0' workingcapitalsubmissionsstartmonth, '0' workingcapitalsubmissionsendmonth, '0' workingcapitalsuccessfulsubmissionsminimum, '0' workingcapitalstartmonth, '0' workingcapitalendmonth, '0' workingcapitalactivationmonthcutoff, '0' adhocshowalluserreports, '0' auditresultslinkstatus, '0' showhistoriccompositesentitysearch, '0' worksheetfullscreenmode, '0' specialcarryforward, null dmsfiletransferformat, null sitelogo, '0' submissionpointsthreshold, '0' enablecompositeaveragebydepartment, '1' viewprevioussubmission, '0' hideauditresultzerovalues, '1' keepreleasedfileimportdata, '0' arlaccountnumberparse, null convertforecastroles, '0' arlenableundo, '0' unhideauditresultsummaryaccountzerovalues, '0' alwaysshowauditresultsummaryaccountcarryforward, '0' compositeaveragesummaryaccounts, '0' onlinebusinessforecast, '0' isfuturedateallowfordatasubmission, '0' dynamicportfolio, '0' enableallocationaccounts, '0' enabletermsandconditions, '0' enablesimultaneousaudit the insert statement generated after configuring things in excel spreadsheet.
my guess ordering of columns in excel spreadsheet doesn't match column order of tablea. column 'isfuturedateallowfordatasubmission' @ 63rd position in tablea table definition, it's @ 77th position in spreadsheet.
if position 63 in spreadsheet you'll find dmsfiletransferformat, in case has value null. i'm guessing source of error.
either arrange order of columns in spreadsheet match order of columns in tablea or explicitly specify columns in insert statement so:
insert tablea (busns_unit_id, busns_unit_nm, allow_rpg_edit_flag, etc.) select '14' busns_unit_id, 'global' busns_unit_nm, '0' allow_rpg_edit_flag, etc
Comments
Post a Comment