Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Cannot insert the value NULL into column

    I am migrating data from one database to another, but coming up against some problems. I am trying to insert records but coming up against this error below

    Msg 515, Level 16, State 2, Line 12
    Cannot insert the value NULL into column 'id', table 'thejanitor.dbo.contracts'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    How can I get around this?

    Below is the code

    Code:
    USE thejanitor
    DECLARE @ClientID INT
    DECLARE @ClientName VARCHAR(50)
    DECLARE SiRPSclients CURSOR FOR
    SELECT id,[name] FROM navitas.dbo.client
    WHERE [name] NOT IN (SELECT DISTINCT [name] FROM thejanitor.dbo.contracts)
    OPEN SiRPSclients
    	FETCH NEXT FROM SiRPSclients 
    	INTO @ClientID, @ClientName	
    	WHILE @@FETCH_STATUS = 0
    BEGIN
    	INSERT INTO thejanitor.dbo.contracts ([name], [name_short], [address_1], [address_2], [city],
    															[post_code], [landlord_display], [contractor_display], [corgi_number], 
    															[sage_code], [warning_email])
    	SELECT name, name_short, address_1, address_2, city, post_code,
    				landlord_display, contractor_display, corgi_number, sage_code, warning_email
    	FROM navitas.dbo.client
    	WHERE id = @ClientID
    						IF @@ERROR <> 0 
    							BEGIN
    								INSERT INTO [Migration].[dbo].[ErrorLogging]
    										   ([ErrorDetails])
    								VALUES
    										   ('error occured when inserting contracts. ~ SiRPS ID:'+ convert(VARCHAR,@ClientID))
    							END
    						ELSE
    							BEGIN
    								DECLARE @OID INT
    								SET @OID = SCOPE_IDENTITY()
    								EXECUTE [Migration].[dbo].[AddDataTolookup] @ClientID,@OID,'client'
    							END
    	FETCH SiRPSclients 
    	INTO @ClientID, @ClientName	
    	END		
    	CLOSE SiRPSclients
    	DEALLOCATE SiRPSclients
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I'll state the obvious and hopefully that'll get us started on getting the full story here.

    How about inserting a value (i.e. not inserting NULL) into the Id column?

    I'm really not sure you need a cursor for this either although I've not read it in enough detail.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JezLisle
    How can I get around this?
    1. insert something else besides NULL, perhaps a character string like 'nuh-uh' or a number like 937937

    2. redefine the column to allow NULLs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just noticed you are looking for SCOPE_IDENTITY. Do you think that Id is an Identity column?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Yes, the ID is an Identity column, this is the case on both the database tables.

    I cant redefine anything as i'm not the owner and its already a live DB.

    The script was allready written by another colleague I am trying to run the scripts now as he has left the business. I have left CURSOR FOR in untill I find out exactly what it is as I've never used it before.

    Is there a better way to do this, allthough I have a short window to get this done in...

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I guess Id is the PK of both the original and destination tables.

    If Id is an Identity column of the destination table, you will also have to add the SET IDENTITY_INSERT thejanitor.dbo.contracts ON/OFF statements.
    Code:
    ....
    
    SET IDENTITY_INSERT thejanitor.dbo.contracts ON 
    WHILE @@FETCH_STATUS = 0
    ...
    
    	INSERT INTO thejanitor.dbo.contracts ([id], [name], [name_short], [address_1], [address_2], [city],
    															[post_code], [landlord_display], [contractor_display], [corgi_number], 
    															[sage_code], [warning_email])
    	SELECT id, name, name_short, address_1, address_2, city, post_code,
    				landlord_display, contractor_display, corgi_number, sage_code, warning_email
    	FROM navitas.dbo.client
    	WHERE id = @ClientID
    
    ...
    DEALLOCATE SiRPSclients
    SET IDENTITY_INSERT thejanitor.dbo.contracts OFF
    
    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

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Excellent thanks for that, it seems to have worked so far

Posting Permissions

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