In this repository, an approach was described as How to import files into SQL database by file streaming technique
First of all, it is necessary to enable the file streaming in SQL server. So in Sql server configuration manager, follow steps as shown in the pictures below
Then in management studio
Second, it is necessary to config the database so in the desired database properties set config below
Now by the means of script below a table should be created. Note that the name of FILESTREAM_ON should be the name of the file group was specified in previews steps --> [DocFileGroup]
CREATE TABLE [dbo].[FILESTREAM_Documents](
[DocumentID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[DocumentName] [varchar](128) NULL,
[DocumentType] [varchar](10) NULL,
[DocumentFS] [varbinary](max) FILESTREAM NOT NULL,
UNIQUE NONCLUSTERED
(
[DocumentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [DocFileGroup]
GO
The primary key in the table should be configured like below
To insert a record in the table by T-Sql 'OPENROWSET' method should be used to convert a file on hard disc to varbinary as below:
DECLARE @File varbinary(MAX);
SELECT @File = CAST(bulkcolumn as varbinary(max))
FROM
OPENROWSET(BULK 'C:\MyFile.pdf', SINGLE_BLOB) as MyData;
INSERT INTO [FILESTREAM_Documents]
VALUES
(
NEWID(),
'MyFile',
'.pdf' ,
@File
)
To retrieve files from a directory, first of all, some options should be enabled First enable advance option in master database.
USE master;
GO
-- To allow advanced options to be changed.
EXECUTE sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
Now, Enable Xp_cmdshell extended stored procedure.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
There are two ways to get the list of folders files in a SQL table.
- Using xp_cmdshell
- Using xp_DirTree
To use xp_cmdshell follow commands below
CREATE TABLE tblgetfileList (excelFileName VARCHAR(100));
INSERT INTO tblgetfileList
EXEC xp_cmdshell 'dir /B "D:\databasefile"';
select * from tblgetfileList
To use xp_DirTree follow commands below
DECLARE @dirPath nvarchar(500) = 'D:\databasefile'
DECLARE @tblgetfileList TABLE
(FileName nvarchar(500)
,depth int
,isFile int)
INSERT INTO @tblgetfileList
EXEC xp_DirTree @dirPath,1,1
SELECT FileName from @tblgetfileList where isFile=1
Now the complete script for retrieve files in a directory and insert into database :
DECLARE @dirPath nvarchar(500) = 'F:\New folder\'
DECLARE @tblgetfileList TABLE
(FileName nvarchar(500)
,depth int
,isFile int)
INSERT INTO @tblgetfileList
EXEC xp_DirTree @dirPath,1,1
--SELECT FileName from @tblgetfileList where isFile=1
DECLARE @FileName VARCHAR(500);
DECLARE @sql NVARCHAR(MAX)
DECLARE @FilePath VARCHAR(500);
DECLARE cursor_file CURSOR
FOR SELECT
FileName
FROM
@tblgetfileList where isFile=1
OPEN cursor_file;
FETCH NEXT FROM cursor_file INTO
@FileName
WHILE @@FETCH_STATUS = 0
BEGIN
print @FileName
SET @FilePath = Concat(@dirPath, @FileName);
SET @sql = '
DECLARE @File varbinary(MAX);
set @File = (SELECT
CAST(
bulkcolumn as varbinary(max)
)
FROM
OPENROWSET(BULK ''' + @FilePath + ''', SINGLE_BLOB) as MyData);
INSERT INTO [FILESTREAM_Documents]
VALUES
(
NEWID(),
''' + SUBSTRING(@FileName, 1, CHARINDEX('.',@FileName) -1 ) + ''',
''' + SUBSTRING(@FileName, CHARINDEX('.',@FileName)+ 1, len(@FileName)) + ''' ,
@File
)'
EXEC sp_executesql @stmt = @sql
FETCH NEXT FROM cursor_file INTO
@FileName ;
END;
CLOSE cursor_file;
DEALLOCATE cursor_file;
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent