Insert text into TEXT column from a file

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 ma开发者_C百科nner? E.g.

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

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.

The answer depends a bit on your use case. I'm guessing you need to load data in at the start of a project... if you need to "feed" data to SQL server then I would have a different answer.

Here are the simplest choices:

• Reformat your text file so it is an sql statement. eg if you have a line of text that you want to insert into a table called names you could put insert into names value(' at the start of each line and ') at the end. Then copy and past that into manager and run it.

• use bulk insert (link)

• use SSIS/DTS (link)

You can import data from a text file using OPENROWSET. But, for this you might have to define a format file as well. You can find more help here