Insert text into TEXT column from a file

Question

What is the easiest way - preferably just using SQL Server Management Studio - to insert text into a Text type column (text, ntext, varchar(max), nvarchar(max)) from a file.

Say a have a table

textFileContent(id, textData)

and want to do

insert into textFileContent(textData)
values([data read from a file, complete file content])

Also, is it possible to do update in a similar manner? E.g.

update textFileContent
set textData = [data read from a file,  complete file content]
where id = 1

Solution

You could use xp_cmdshell:

create table #mytable 
(
    id int IDENTITY(1,1) NOT NULL,
    result nvarchar(MAX) NULL
)

insert into #mytable (result)
exec xp_cmdshell 'type c:\somefile.txt' -- this may require cmd before it

This should dump the text in to the table, where hard returns represent new records.

Otherwise, if you have SQL Express you are limited to BCP and BULK INSERT. There are commands in BCP if you create your table first to generate your format file. Remember if it is Unicode that your delimiters are two characters \n\0\r\0 (or something like that)

Otherwise you could use SSIS if you have SQL Standard or above. You create a Data Flow, drop an icon for the input text file, drop an icon for the destination table, and click play. To make this work with SQL, you will need to call it with something like that xp_cmdshell above. SSIS is the visual of what you would do with BCP as far as text files are concerned.