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
)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)
That is really all there is to it – happy coding
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.