Importing files to SQL

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

👉 Step 1:

👉 Step 2:

👉 Step 3:

👉 Step 4:

👉 Step 5:

Then in management studio

👉 Step 6:

Second, it is necessary to config the database so in the desired database properties set config below

👉 Step 1:

👉 Step 2: Adding file group

👉 Step 3:

👉 Step 4: Adding DB file

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;