I'm trying to use a Bulk Insert on a memo field.
I've hand coded the building of the text file because COPY TO will not copy memo fields.
I've modified the field terminator and row terminator but I need to bring in character fields as varchar that are greater than 256 characters.

This seems to be a limitation.

I've built a script and which I've added below.

What is the work around for this problem?

TIA

ALTER TABLE NotePad ADD LOANID INT NULL 
GO 

CREATE TABLE #Temp_NotePad(MUNIQ_ID INT , LOANNO CHAR(10) NOT NULL, LOANID INT , DATE_ENTER DATETIME NULL , TIME_STAMP CHAR(5) NOT NULL, USER1 CHAR(4) NOT NULL, COMMENT1 VARCHAR(2000) NOT NULL )
Go

BULK INSERT #Temp_NotePad FROM 'Y:\SqlData1\Resg\Wconv\MistToDri_Conversion\MistConversion_NotePad.txt' WITH (FIELDTERMINATOR = '|',  ROWTERMINATOR = '')
Go

UPDATE #Temp_NotePad SET #Temp_NotePad.DATE_ENTER= NULL WHERE DATE_ENTER IN('9999-12-31 00:00:00.000','9999-12-31 12:00:00.000') 
GO 

INSERT INTO NotePad(MUNIQ_ID, LOANNO, LOANID, DATE_ENTER, TIME_STAMP, USER1, COMMENT1)
     SELECT #Temp_NotePad.MUNIQ_ID, #Temp_NotePad.LOANNO, #Temp_NotePad.LOANID, #Temp_NotePad.DATE_ENTER, #Temp_NotePad.TIME_STAMP, #Temp_NotePad.USER1, #Temp_NotePad.COMMENT1
     FROM #Temp_NotePad
     INNER JOIN MASTER ON #Temp_NotePad.MUNIQ_ID = MASTER.UNIQ_ID 
     WHERE MUNIQ_ID NOT IN ( SELECT MUNIQ_ID FROM NotePad )
GO 

DROP TABLE #Temp_NotePad
GO