Results 1 to 8 of 8

Thread: insert fails

  1. #1
    Join Date
    Mar 2004
    Posts
    25

    Angry Unanswered: insert fails

    INSERT INTO ticket_dump_datawarehouse
    SELECT Ticket_ID,
    ID_Entry_Type,
    Region,
    Related_Case,
    Create_Date,
    ID_Service_Center,
    ID_Type,
    ID_Priority
    FROM dump_view
    will fill fail >

    cannot insert the value NULL into column 'Ticket_ID', table 'capacity_base_tracking.dbo.ticket_dump_datawareho use'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    But dump_view does not contain any Ticket_ID columns that have NULL value..

    where is the problem

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You're missing telling us the DDL of the tables involved.
    If you aren't specifying one of the columns in ticket_dump table, SQL Server will try to insert a NULL.

    My best guess is that the referred table has more columns than you are
    supplying values for. When you perform an INSERT, SQL Server has to have a way to determine a value for ever y column in the new row(s).

    You either supply specific values, or you have defined a default value for the column when you created the table, or the column may allows NULLs.

    In your case, it looks like there is another column you are not specifying, which has no default and doesn't allow NULLs, so SQL Server can't figure out what value to put into that column. That is why you are getting the error.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Mar 2004
    Posts
    25
    I don'n want to give you DDL of reffered table because it has 56 columns

    I just put part of the query there for illustration.

    I checked this table and all rows allow null inserts except ID_Ticket and


    Ticket_ID is the primary key.. that's the important thing I forgot to mention ..

    I think I should set enable identity insert or something like that

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Is that field enabled a Identity column>
    If so you should enable with SET IDENTITY_INSERT ON, as stated in BOL.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Mar 2004
    Posts
    25

    :(

    no it not an identity column ...

    just a primary key.

  6. #6
    Join Date
    Mar 2004
    Location
    France
    Posts
    4

    Re: :(

    It's the pb !!!

    you cannot have the value zero with a primary key... when you define a column as primary key you shouldn't have any zero value in this column.


    Originally posted by mikosan
    no it not an identity column ...

    just a primary key.

  7. #7
    Join Date
    Mar 2004
    Posts
    25
    yeah but problem is I don't want to insert any null values into this column

  8. #8
    Join Date
    Mar 2004
    Location
    France
    Posts
    4
    Can you see what is the value that you want to insert into the colum Ticket_ID ?

    because even if you don't want to insert a zero in this column, may be the problem appear before you insert the value...but may be when you define this value...and you should resolve the problem here.

Posting Permissions

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