Reputation: 4296
Cannot insert the value null error but value isn't actually null
I have an error that seems simple to resolve, but I have not been able to do so. When I run a statement to insert one record into a table I receive the following error:
Cannot insert the value NULL into column 'IsFutureDateAllowForDataSubmission', table 'TableA'; column does not allow nulls. INSERT fails.
Simple, right? I am trying to insert null into a column that doesn't allow it. However in the INSERT statement I am explicitly inserting a value for that column. Nothing fancy, no dynamic SQL, no subqueries or anything like that. I have a simple statement with every value enumerated. This is failing with the above error
TableA consists of 81 columns. It is the configuration table for a large web application. The column does not allow nulls. The column data type is a bit (a TRUE/FALSE flag for the application). I cannot alter the design of the 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 am passing in 0 for IsFutureDateAllowForDataSubmission, I also tried '0'. Both result in the same error.
I haven't found much online so far other than people trying to add values in columns that are set as an identity, but that scenario is not relevant here as the column I am trying to add a value to is a simple 0/1 flag, not an identity.
I would be happy to provide more information if required. The actual insert statement is long (81 columns that I am explicitly assigning values to).
Update
Here is the 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 is the full INSERT statement that generates the error:
INSERT INTO TableA SELECT
'14' AS BUSNS_UNIT_ID,
'Global' AS BUSNS_UNIT_NM,
'0' AS ALLOW_RPG_EDIT_FLAG,
'1' AS FISCAL_YR_START_MTH,
'1' AS INPUT_FORM_EXCEL_FLAG,
'0' AS SHOW_COMPOSITE_CALCULATION_TYPE,
'ANNUALIZATION' AS DEFAULT_COMPOSITE_CALCULATION_TYPE,
'0' AS MAP_ALL_DEPT_FLAG,
'3' AS COMPST_MIN_DLRS_TO_RUN,
'Cumulative' AS ACCT_DATA_TYP,
'Period' AS DECIMAL_SEPARATOR_DEFAULT,
'0' AS FORM_READONLY_FLAG,
'25' AS CMPST_RUN_MTHS,
'3' AS CMPST_RUN_YRS,
'1' AS DISPLAY_LCL_ACCT_NBR,
',' AS csv_delimiter,
'0' AS SHOW_PLANS,
NULL AS INPUT_FORM_TEMPLATE_NAME,
'4' AS COMPST_RUN_REVIEWSTATUS_DEFAULT,
'0' AS AllowDuplicateAccountData,
'0' AS FILE_IMPORT_SCHEMA,
'1' AS ALLOW_DELETE_COMPOSITE,
'0' AS Disable_Multiple_StarMap,
'0' AS AllowMasterAccountMap,
'1' AS ShowCompositesToVendors,
'0' AS AllowCompositeClosedSnapShot,
'1' AS SubmissionFrequencyID,
'0' AS AutoRecalculate,
'0' AS ShowDealerCodeAlternate,
'0' AS ShowSubDealerIndicator,
'0' AS ShowDealerType,
'0' AS OnlineBusinessPlan,
'1' AS ShowInactiveItemsAdvancedCriteria,
'SUBMISSION' AS HonorRollType,
'0' AS ShowInactiveDealersDataSubmission,
'0' AS AllowFinancialDataSuppression,
'1' AS AllowDataSubmissionComments,
'1' AS RejectMatchingSubmissions,
'0' AS HonorRollAccuracyPoint,
'0' AS HonorRollNonSubmissionPoint,
'0' AS DisableInputFormAccountHighlight,
'0' AS DisableInputFormAuditComments,
'0' AS EnableSubDealers,
'1' AS ShowCompositesInDealerProfile,
'0' AS EnableCarryForwardAuditRemark,
'0' AS EnableInputFormInlineEvents,
'0' AS PreventOverMap,
'0' AS DefaultAdhocReportVariablesTab,
'' AS WorkingCapitalFormula,
'' AS InvestmentGuideFormula,
'0' AS DualDealerAppointmentMonthCutoff,
'0' AS WorkingCapitalSubmissionsStartMonth,
'0' AS WorkingCapitalSubmissionsEndMonth,
'0' AS WorkingCapitalSuccessfulSubmissionsMinimum,
'0' AS WorkingCapitalStartMonth,
'0' AS WorkingCapitalEndMonth,
'0' AS WorkingCapitalActivationMonthCutoff,
'0' AS AdhocShowAllUserReports,
'0' AS AuditResultsLinkStatus,
'0' AS ShowHistoricCompositesEntitySearch,
'0' AS WorkSheetFullScreenMode,
'0' AS SpecialCarryForward,
NULL AS DmsFileTransferFormat,
NULL AS SiteLogo,
'0' AS SubmissionPointsThreshold,
'0' AS EnableCompositeAverageByDepartment,
'1' AS ViewPreviousSubmission,
'0' AS HideAuditResultZeroValues,
'1' AS KeepReleasedFileImportData,
'0' AS ArlAccountNumberParse,
NULL AS ConvertForecastRoles,
'0' AS ArlEnableUndo,
'0' AS UnhideAuditResultSummaryAccountZeroValues,
'0' AS AlwaysShowAuditResultSummaryAccountCarryForward,
'0' AS CompositeAverageSummaryAccounts,
'0' AS OnlineBusinessForecast,
'0' AS IsFutureDateAllowForDataSubmission,
'0' AS DynamicPortfolio,
'0' AS EnableAllocationAccounts,
'0' AS EnableTermsAndConditions,
'0' AS EnableSimultaneousAudit
The INSERT statement is generated after configuring things in an Excel spreadsheet.
Upvotes: 1
Views: 1478
Answers (2)
Reputation: 9166
The columns in your insert statement are out of sync with the columns in the table. The fact that you name the columns in the select part of your insert statement will not cause the insert to align (for lack of a better word) the columns to the table definition.
When doing an insert like the one you do here, the columns in the select statement must be in the correct order with regards to the table you are inserting to. Checking your table definition and comparing to the select, we have on row 63 in the table:
| IsFutureDateAllowForDataSubmission |
while in the select we have:
NULL AS DmsFileTransferFormat
So you are trying to insert null into the column. To fix the problem you can either rearrange the columns in the select statement so that they are in the same order as in the table definition, or you can add a column order before the select:
insert into TableA (BUSNS_UNIT_ID, ...)
select ...
and then make sure that you list the column names within the parentheses in the same order as they appear in the subsequent select.
Upvotes: 1
Reputation: 220
My guess is the ordering of your columns in your Excel spreadsheet doesn't match the column order of your TableA. Your column 'IsFutureDateAllowForDataSubmission' is at the 63rd position in your TableA table definition, but it's at the 77th position in your spreadsheet.
If you look up position 63 in your spreadsheet you'll find DmsFileTransferFormat, which in your case has value NULL. I'm guessing this is the source of your error.
Either arrange the order of the columns in your spreadsheet to match the order of the columns in TableA or explicitly specify the columns in your INSERT statement like so:
INSERT INTO TableA (BUSNS_UNIT_ID, BUSNS_UNIT_NM, ALLOW_RPG_EDIT_FLAG, etc.) SELECT
'14' AS BUSNS_UNIT_ID,
'Global' AS BUSNS_UNIT_NM,
'0' AS ALLOW_RPG_EDIT_FLAG,
etc
Upvotes: 3
Related Questions
- Fix "cannot insert NULL into" SQL Error on table
- SQL Server query making null control
- Cannot insert the value NULL into column X, column X does not allow nulls. INSERT fails.
- SQL Server Insert Null error in spite of providing not null values
- SQL Insert Into cannot insert NULL
- Cannot insert the value NULL into column XXX. INSERT fails. Insert valid only
- cannot insert null value in id column
- Cannot insert the value NULL into column - But No Null values exist
- SQL Server NULL Values with an Insert statement
- SQL Server : "cannot insert the value NULL into column" but column is not null