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.