INSERT Statement error message references column I am not including in query
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