Referencing Other Tables for an SQL Select statement
===

I have two tables and they are connected via the 'debt_key' here are the table structures

Note Table:
Note_Key (numeric(9,0),not null)
Debtor_Key(numeric(9,0),not null)
Debt_Key(numeric(9,0),not null)
User_Key(numeric(5,0),not null)
Note_Date(datetime, not null)
Note_Time(char(8),not null)
Note_Text(Char(80),not null)

Debt Table:
Debt_key
ACCT
....and so on, but we only need to use those two fields.


So I am trying to add new notes with a date and time into the Note table. So far I have been unsuccsessful. Here is the basic outcome of what I have written:

INSERT INTO NOTE (b.NOTE_DATE, b.NOTE_TIME, b.NOTE_TEXT)
VALUES('2012-07-11 00:00:00.000','00:00:00','ASSIGNED FOR PI CONTACT')
   SELECT NOTE_DATE, NOTE_TIME, NOTE_TEXT 
   FROM DEBT a JOIN NOTE B ON B.DEBT_KEY = A.DEBT_KEY AND A.DEBT_KEY = '5797071'

When I execute it is giving me this error:

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

I cannot add a value to thic column though as it is auto incremented. Any ideas?


Thanks.