How to insert the contents of a text file into a table in SQL Server

Question

I have several files (they are XML but that's not important) that need to be inserted into an existing SQL table (i.e. I didn't design it.) The table looks like this.

ReportType
  ID (int) <- identity
  Name (varchar(32))
  TransformXSLT (nvarchar(max))

Normally I would do:

INSERT INTO ReportType (Name, TransformXSLT)
VALUES ('template name', '<lots><of><xml><goes><here>...</lots>')

Is there any way to do:

INSERT INTO ReportType (Name, TransformXSLT)
VALUES ('template name', {filename})

I'm using SQL Server Management Studio and Eclipse+Maven to manage the files.

Answer 1

BULK INSERT or OPENROWSET(BULK…) are the usual options from T-SQL

After comment...

...FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB);

and the "Bulk Exporting or Importing SQLXML Documents" section here

Sorry, I've not actually tried this but MSDN says you can

Answer 2

Have you tried using the SQL Server Import and Export Wizard?

Go into SQL Server Management Studio. In Object Explorer, right click the database then Tasks > Import Data....

This will let you import data as a one off exercise, or let you save the resulting SSIS package and re-run it.

Give it a go.