Why does the following insert statement produce the error that follows the
code.

=============================
INSERT INTO diary
(
di_user, di_notes,
di_dest_pk, di_dest_fm
)
SELECT
froi.fr_efroi,
'Claim: '+froi.fr_claimno,
froi.fr_mco_itn,
'FROI'
FROM
froi
LEFT JOIN diary ON LEFT(diary.di_dest_pk,20) = froi.fr_mco_itn
WHERE
diary.di_dest_pk IS NULL AND
froi.fr_edi_sta = 'N' AND
froi.fr_mco_dt < @a_date
===============================
Error:

Server: Msg 8626, Level 16, State 1, Line 10
Only text pointers are allowed in work tables, never text, ntext, or image
columns. The query processor produced a query plan that required a text,
ntext, or image column in a work table.
===============================

The line in red is the one actually causing the error message. If I remove
the "+froi.fr_claimno", it works.

The fr_claimno is defined as follows: [FR_CLAIMNNO] [char] (10) NOT NULL
The field being updated with that information is defined as: [DI_NOTES]
[text]

Am I breaking a rule by inserting data into a field defined as [text]?

TIA

Guess the query plan formulated is building a work table which includes the text column. Try rearranging the query - maybe something like this where froi.pk is whatever the pk on froi is

INSERT INTO diar

di_user, di_notes
di_dest_pk, di_dest_f

SELEC
froi.fr_efroi
'Claim: '+froi.fr_claimno
froi.fr_mco_itn
'FROI
FRO
fro
joi
(select froi.p
from fro
LEFT JOIN diary ON LEFT(diary.di_dest_pk,20) = froi.fr_mco_it
WHER
diary.di_dest_pk IS NULL AN
froi.fr_edi_sta = 'N' AN
froi.fr_mco_dt < @a_dat
)
on froi.pk = a.p