Fast inserting a file into a table

| category: Programming | author: st
Tags:

How to insert fast a file (i.e. binary one) from disk to a SQL Server table?

Certainly the SQL Server account should have a corresponding permissions to access the file on the disk. For example I take the file C:\WINDOWS\system32\oembios.bin having the size about 12 MB. For really big files you'd better switch to the FILESTREAM approach.

CREATE TABLE dbo.filestore (
  id int PRIMARY KEY,
  name nvarchar(255),
  content varbinary(max)
)
GO

INSERT INTO dbo.filestore (id, name, content)
SELECT 1 AS id, 'oembios.bin' AS name, src.BulkColumn AS content
FROM OPENROWSET(BULK N'C:\WINDOWS\system32\oembios.bin', SINGLE_BLOB) AS src