CODEWITHSUNDEEP
sqlinsert
garincha.03
garincha.03

Reputation: 23

Insert data from table1 to table 2 where is user id and record id

so I need to insert some records from table1 to table2 depending on their user_id (PVKEY) and record_id (CNKEY). Just to explain better, every user has his own unique user_id (PVKEY) and every record has his own record_id (CNKEY). One user can have couple of records but everytime when the new record is added it needs to get new record_id (CNKEY) Everytime I try to insert I get message:

Cannot insert the value NULL into column 'CNKEY', table 'Progress.dbo.CONTHIST'; column does not allow nulls. INSERT fails. The statement has been terminated.

user_id (PVKEY): data and system type is float, primary key FALSE, allow nulls TRUE. record_id (CNKEY): data and system type is float, primary key TRUE, allow nulls FALSE, record_id (CNKEY) exists only in table2 but not in table1 and user_id (PVKEY) exists in both tables. So here is my example that I was trying to execute it without any luck:

INSERT INTO dbo.CONTHIST (PVKEY, CONTTYPE, ASSIGNEDTO, CONTDATE, SOURCE, NOTES, CNKEY)
       SELECT Pvkey, ContactType, AssignedTo, Date, SourceCode, ContactStatus
       FROM MopUpEOC
       WHERE PVKEY in (5,7,11)

I hope there is some good soul who will help me, I would appreciate it a lot! :D

Upvotes: 0

Views: 152

Answers (2)

Jens
Jens

Reputation: 3299

The problem is that you aren't passing CNKEY as a value for your insert, but you defined it as one of your columns to be inserted.

INSERT INTO dbo.CONTHIST (PVKEY, CONTTYPE, ASSIGNEDTO, CONTDATE, SOURCE, NOTES, CNKEY) -- You defined it here!
       SELECT Pvkey, ContactType, AssignedTo, Date, SourceCode, ContactStatus -- But aren't adding it here
       FROM MopUpEOC
       WHERE PVKEY in (5,7,11)

If this CNKEY value should be a new numeric value, you can change it in your MopUpEOC table to be an IDENTITY field, and remove it from your "INSERT INTO ..." line. That way SQL Server will automatically add an incremental numerical value to that column for every record, which will be unique.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The error is pretty clear: CNKEY in the matching records is NULL, as least on one occasion.

You have basically two options:

  • Change CNKEY to allow NULL values.
  • Add in some sort of default in the statement.

The latter would use COALESCE(), like this:

INSERT INTO dbo.CONTHIST (PVKEY, CONTTYPE, ASSIGNEDTO, CONTDATE, SOURCE, NOTES, CNKEY)
   SELECT Pvkey, ContactType, AssignedTo, Date, SourceCode,
          COALESCE(ContactStatus, '') -- Or whatever the appropriate value would
   FROM MopUpEOC
   WHERE PVKEY in (5, 7, 11)

Upvotes: 2

Related Questions