Importing a file into a Sql Server varbinary column

 

Previously I’ve mostly achieved this by using Powershell, but when the file you want to import resides on the Sql server itself (or somewhere accessible to it) it can actually be done with just a few T-SQL statements.

However I need to dynamically name the the file to be imported, and that takes an extra hoop to jump through because the OPENROWSET doesn’t take variable names as a parameter for the file name. There are a good deal of suggestions on the web on how to do this, but I favour simplicity so my solution really tries to cut it down to the bare necessities.

First i created a “Documents” table for storing my files:

CREATE TABLE [dbo].[Documents](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date]  AS (getdate()),
    [FileName] [varchar](512) NOT NULL,
    [DocumentData] [varbinary](max) NOT NULL,
CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

Then the import function:

DECLARE @FileName VARCHAR(200) = ‘e:\Wheat_vehicles_combine_harvester_farming_john_deere_1920x1200.jpg’;

DECLARE @sql varchar(max) = ‘SELECT ”’ + @filename + ”’,* FROM OPENROWSET(BULK N”’ + @fileName + ”’, SINGLE_BLOB) AS RS’;

INSERT INTO PERH..Documents (FileName, DocumentData)
    EXEC(@sql);

That is really all there is to it – happy coding Smile

PS. Checking for file existence, deleting source file after loading etc can be achieved via the extended stored procedures on your system such as xp_fileexist, xp_delete_file.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s