SQL Server – INSERT Statement Error References Column Not in Query

sql serversql-server-2008

I am using MSSQL 2008.

My INSERT statement is as follows:

INSERT INTO [DB1].[dbo].[SpotArtSong](ID)
SELECT  SNG.S_ID
FROM [DB1].[dbo].[SpotArtSong] SPOT
INNER JOIN [DB2].[dbo].[nt-art] ART 
ON ART.Artist_Name LIKE SPOT.[Artist]+'%'
INNER JOIN [DB2].[dbo].[nt-sng] SNG 
ON ART.Artist_ID  = SNG.Artist_ID
AND left(SNG.SONG_TITLE,10) = left(SPOT.[Song],10)
WHERE spot.BDSID is NULL

Column [ID] on [DB1].[dbo].[SpotArtSong] has some null values that I am trying to replace. I have found the replacements and when looking over the full column results, I am happy with what the search would return from SNG.S_ID. I am trying to insert that one column into the one column on [DB1].[dbo].[SpotArtSong].

Essentially, I scrapped some data into [DB1].[dbo].[SpotArtSong] and I am matching it to some existing production data split among [DB2].[dbo].[nt-art] (ART) and [DB2].[dbo].[nt-sng] (SNG). I know the data in ART and SNG is correct, I am correlating it with the scrapped data in SPOT

The failure statement says:

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

I am not inserting anything there so I am not sure why it would fail. Plus, if I comment out the insert statement, the select statement does not return any NULL values.

Best Answer

The table SpotArtSong which you are trying to insert some records has at least these columns:

  • ID
  • Artist

You are just supplying a value for ID, but column Artist has a NOT NULL constraint. When you don't supply a value for a column (by omitting it from the INSERT list), NULL is inserted by default unless there is a DEFAULT constraint. Because this column doesn't allow NULL values, the insert statement fails.

Either supply a value for the Artist column (and any other column that is NOT NULL) or change the NOT NULL constraint on that column.

To supply the artist name, add the Artist column name to the INSERT and supply a value through the SELECT, like the following:

INSERT INTO [DB1].[dbo].[SpotArtSong](
    ID,
    Artist)
SELECT  
    ID = SNG.S_ID,
    Artist = ART.Artist_Name
FROM 
    [DB1].[dbo].[SpotArtSong] SPOT
    INNER JOIN [DB2].[dbo].[nt-art] ART ON ART.Artist_Name LIKE SPOT.[Artist]+'%'
    INNER JOIN [DB2].[dbo].[nt-sng] SNG ON 
        ART.Artist_ID  = SNG.Artist_ID AND 
        left(SNG.SONG_TITLE,10) = left(SPOT.[Song],10)
WHERE 
    spot.BDSID is NULL

To change the NOT NULL constraint use ALTER TABLE (note the lack of NOT NULL at the end):

ALTER TABLE [DB1].[dbo].[SpotArtSong] ALTER COLUMN Artist VARCHAR(100)

Althought it seems to me that you are actually trying to do an UPDATE instead of an INSERT:

UPDATE SPOT SET
    ID = SNG.S_ID
FROM 
    [DB1].[dbo].[SpotArtSong] SPOT
    INNER JOIN [DB2].[dbo].[nt-art] ART ON ART.Artist_Name LIKE SPOT.[Artist]+'%'
    INNER JOIN [DB2].[dbo].[nt-sng] SNG ON 
        ART.Artist_ID  = SNG.Artist_ID AND 
        left(SNG.SONG_TITLE,10) = left(SPOT.[Song],10)
WHERE 
    spot.BDSID is NULL
Related Question