INSERT Statement error message references column I am not including in query

Question

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.

Answer 1

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

Answer 2

Column [ID] on [DB1].[dbo].[SpotArtSong] has some null values that I am trying to replace.

Then why are you inserting whole new rows?

The insert statement inserts new rows.

You're telling the database to create new rows containing only the ID values that you're going to supply. The database is [rightly] objecting because the Artist column must have a value and, in the values you're supplying, there isn't one.

The update statement updates existing rows.

I think that's the one you need.