Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2016
    Posts
    2

    Unanswered: Insert datas from table1 to table2 depending on 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.
    Just to be sure that you'll understand the question better: 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:

    Code:
    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!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,425
    Provided Answers: 4
    Here I count 7 columns (PVKEY, CONTTYPE, ASSIGNEDTO, CONTDATE, SOURCE, NOTES, CNKEY)
    Here I count 6 columns Pvkey, ContactType, AssignedTo, Date, SourceCode, ContactStatus

    You must add an extra column in the SELECT statement.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2016
    Posts
    2
    Quote Originally Posted by Wim View Post
    Here I count 7 columns (PVKEY, CONTTYPE, ASSIGNEDTO, CONTDATE, SOURCE, NOTES, CNKEY)
    Here I count 6 columns Pvkey, ContactType, AssignedTo, Date, SourceCode, ContactStatus

    You must add an extra column in the SELECT statement.
    Thank you but I'm not concerned about that because if I remove CNKEY it won't insert because CNKEY is primary key and when I'm tring to insert data it says:

    Cannot insert the value NULL into column 'CNKEY', table 'Progress.dbo.CONTHIST'; column does not allow nulls. INSERT fails. The statement has been terminated.
    This is my problem, I can't insert it because CNKEY doesn't allows NULL and it needs to be insert some kind of value because every record needs to have his own CNKEY. I hope it's more understandable now.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So ehere ard you setting the value of cnKey?
    Its not in your select. So it will have a null value and you have specified not null...
    Your call

    Also as wim says you have specified 7 columns for the insert but only getting 6 from the select.

    The error meesage should be explicit enough
    The insert fails because cnkey is null. Get / Set a value for cnkey...
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Posts
    1,425
    Provided Answers: 4
    Can you show the CREATE TABLE script for the dbo.CONTHIST table?

    If the script is something like :
    Code:
    CREATE TABLE dbo.CONTHIST(
    	CNKEY	INT		NOT NULL,
    	PVKEY	INT		NOT NULL,
    	...
    	CONSTRAINT pk_CONTHIST PRIMARY KEY (CNKEY)
    )
    You MUST provide a value for CNKEY, even when you're not really concerned about that , the system is concerned that you provide a valid value for it (each value must be unique) and will enforce it.


    If the script is something like :
    Code:
    CREATE TABLE dbo.CONTHIST(
    	CNKEY	INT		NOT NULL IDENTITY(1, 1),
    	PVKEY	INT		NOT NULL,
    	...
    	CONSTRAINT pk_CONTHIST PRIMARY KEY (CNKEY)
    )
    Due to the presence of "IDENTITY", you do not need to provide a value for CNKEY, SQL Server will do that for you. You even can't provide a value for it, the system will give an error if you'd try.

    If you change the CREATE SCRIPT so that CNKEY becomes an Identity column, you will have to change your INSERT script to:
    Code:
    INSERT INTO dbo.CONTHIST (PVKEY, CONTTYPE, ASSIGNEDTO, CONTDATE, SOURCE, NOTES)
           SELECT Pvkey, ContactType, AssignedTo, Date, SourceCode, ContactStatus
           FROM MopUpEOC
           WHERE PVKEY in (5,7,11)
    (Remove CNKEY from the list of columns).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •