Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Cannot insert the value null into column 'Id' using sf_bulkops

Hi, Trying to do a data upload to a custom object. I set up the stored procedure the same way as one in production and we are getting a Cannot insert the value NULL into column 'Id' error when trying to upload. DBAmp version: 3.5.1

code snippit:

BEGIN TRANSACTION
--delete Interaction__c_Load
delete from Interaction__c_Load

--load Interaction__c_Load
insert Interaction__c_Load(Activity_Date__c,Activity_Group__c,Activity_Type__c,Business_Unit__c,Company__c,
Interaction_Purpose__c,Name,Non_Prudential_Attendees__c,Notes__c,Prudential_Attendees__c,Prudential_Rep__c)
(SELECT DISTINCT activitydate,activity_group,activitytype,business_unit,salesforce_company_id,
purpose,interaction_name,nonpruattendees,note,pruattendees,prurepname
from [KEYCLIENTS_LS].[KeyClients].[dbo].[kds_activity]
where salesforce_company_id is not null
)

--delete from Interaction__c_Delete
delete from Interaction__c_Delete

--insert Interaction__c_Delete
insert Interaction__c_Delete(Id,OwnerID)
(select Id,OwnerID from Interaction__c)

COMMIT

--exec sf_bulkops
exec sf_bulkops 'HardDelete:bulkapi','SF_GIRG_QA','Interaction__c_Delete'
--check for errors
if (select count(*) from Interaction__c_Delete where Error not like '%Operation Successful%') > 0
begin
select @err_msg = (select distinct min(error) from Interaction__c_Delete
where Error not like '%Operation Successful%'
and error is not null)
RAISERROR(@err_msg, 16, 1)
end

exec sf_bulkops 'Insert:bulkapi','SF_GIRG_QA','Interaction__c_Load'
--check for errors
if (select count(*) from Interaction__c_Load where Error not like '%Operation Successful%') > 0
begin
select @err_msg = (select distinct min(error) from Interaction__c_Load
where Error not like '%Operation Successful%'
and error is not null)
RAISERROR(@err_msg, 16, 1)
end

-- Refresh table Interaction__c in database
exec SF_Refresh SF_GIRG_QA,Interaction__c
1 person has
this question
+1
Reply
  • Can I see the complete message output showing the error after this has been run?

    The most likely cause is the input table has an Id column that does not accept nulls and when populating that table, there is a null that is being selected and trying to be inserted into the Id column that does not accept nulls.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Hi Justin,

    Thanks for the response. The only Id field is the custom objects Id, Interaction__c.Id, none of the other firld are required.Am I assuming correctly that when bulk opps is used for inserts, it populates the "main" id, Interaction__c.Id?

    Job 'Upload interaction__c_SF_GIRG' : Step 3, 'Upload interaction__c' : Began Executing 2018-10-24 14:29:16

    Msg 515, Sev 16, State 2, Line 14 : Cannot insert the value NULL into column 'Id', table 'SF_GIRG.dbo.Interaction__c_Load'; column does not allow nulls. INSERT fails. [SQLSTATE 23000]
    Msg 3621, Sev 16, State 1, Line 14 : The statement has been terminated. [SQLSTATE 01000]
    --- Starting SF_BulkOps for Interaction__c_Delete V3.5.1 [SQLSTATE 01000]
    14:29:17: Run the DBAmp.exe program. [SQLSTATE 01000]
    14:29:17: DBAmp Bulk Operations. V3.5.1 (c) Copyright 2006-2017 forceAmp.com LLC [SQLSTATE 01000]
    14:29:17: Hard Deleting Id's using Interaction__c_Delete (PAERSCBD3003\SALESFORCE_D1 / SF_GIRG) . [SQLSTATE 01000]
    14:29:17: DBAmp is using the SQL Native Client. [SQLSTATE 01000]
    14:29:17: Warning: BulkAPI operations without a Sort column could perform slowly. [SQLSTATE 01000]
    14:29:17: SF_Bulkops will poll every 60 seconds for up to 3600 seconds. [SQLSTATE 01000]
    14:29:17: 0 rows read from SQL Table. [SQLSTATE 01000]
    14:29:17: 0 rows processed. [SQLSTATE 01000]
    14:29:18: Job Complete. [SQLSTATE 01000]
    14:29:18: Retrieving Job Status. [SQLSTATE 01000]
    14:29:18: 0 rows read from SQL Table. [SQLSTATE 01000]
    14:29:18: 0 rows successfully processed. [SQLSTATE 01000]
    14:29:18: 0 rows failed. [SQLSTATE 01000]
    14:29:18: 0 rows marked with current status. [SQLSTATE 01000]
    --- Ending SF_BulkOps. Operation successful. [SQLSTATE 01000]
    --- Starting SF_BulkOps for Interaction__c_Load V3.5.1 [SQLSTATE 01000]
    14:29:18: Run the DBAmp.exe program. [SQLSTATE 01000]
    14:29:18: DBAmp Bulk Operations. V3.5.1 (c) Copyright 2006-2017 forceAmp.com LLC [SQLSTATE 01000]
    14:29:18: Inserting Interaction__c_Load (PAERSCBD3003\SALESFORCE_D1 / SF_GIRG). [SQLSTATE 01000]
    14:29:18: DBAmp is using the SQL Native Client. [SQLSTATE 01000]
    14:29:18: Warning: BulkAPI operations without a Sort column could perform slowly. [SQLSTATE 01000]
    14:29:18: SF_Bulkops will poll every 60 seconds for up to 3600 seconds. [SQLSTATE 01000]
    14:29:18: 0 rows read from SQL Table. [SQLSTATE 01000]
    14:29:18: 0 rows processed. [SQLSTATE 01000]
    14:29:18: Job Complete. [SQLSTATE 01000]
    14:29:18: Retrieving Job Status. [SQLSTATE 01000]
    14:29:18: 0 rows read from SQL Table. [SQLSTATE 01000]
    14:29:18: 0 rows successfully processed. [SQLSTATE 01000]
    14:29:18: 0 rows failed. [SQLSTATE 01000]
    14:29:18: 0 rows marked with current status. [SQLSTATE 01000]
    --- Ending SF_BulkOps. Operation successful. [SQLSTATE 01000]
    --- Starting SF_Refresh for Interaction__c V3.5.1 [SQLSTATE 01000]
    14:29:19: Using last run time of 2018-10-24 13:59:00 [SQLSTATE 01000]
    14:29:19: Identified 0 updated/inserted rows. [SQLSTATE 01000]
    14:29:19: Identified 0 deleted rows. [SQLSTATE 01000]
    --- Ending SF_Refresh. Operation successful. [SQLSTATE 01000]
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • It looks like the error is coming on this statement, when you are trying to populate the Interaction__c_Load table:

    --load Interaction__c_Load
    insert Interaction__c_Load(Activity_Date__c,Activity_Group__c,Activity_Type__c,Business_Unit__c,Company__c,
    Interaction_Purpose__c,Name,Non_Prudential_Attendees__c,Notes__c,Prudential_Attendees__c,Prudential_Rep__c)
    (SELECT DISTINCT activitydate,activity_group,activitytype,business_unit,salesforce_company_id,
    purpose,interaction_name,nonpruattendees,note,pruattendees,prurepname
    from [KEYCLIENTS_LS].[KeyClients].[dbo].[kds_activity]
    where salesforce_company_id is not null
    )

    I'm assuming there is a column in this table named Id that does not allow nulls. But, it looks like to me you are not populating the Id field as it is not in the insert or select statement above. When the data that is trying to be inserted into this table via the select statement happens, the Id is getting defaulted to null since it isn't in this statement. I think this is what is causing the SQL error you are seeing above.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Jason is this statement correct? When bulk opps is used for inserts, it populates the "main" id of the object, Interaction__c.Id? As I said in my last post, this is the only id field that would have a null value. Thanks.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • The error you are getting is a SQL error because the Id column in the Interaction__c_Load table is defined as not null. If you change that column definition to allow nulls, then DBAmp will populate it when the SF_BulkOps runs.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated