Проблема: Мне необходимо импортировать несколько документов и файлов в базу данных с включенным FILESTREAM. Как я могу это сделать эффективно?

Решение: В этой статье мы рассмотрим, как импортировать различные документы, изображения и файлы журналов из определенного каталога в таблицу с включенным FILESTREAM в SQL Server.

Шаг 1: Включение FILESTREAM в SQL Server

Прежде чем мы сможем продолжить импорт файлов, нам необходимо включить FILESTREAM в SQL Server. Это можно сделать, следуя указанным в документации инструкциям.

Шаг 2: Создание таблицы с включенным FILESTREAM

Создайте таблицу с включенным FILESTREAM в базе данных для хранения документов. Таблица должна иметь столбец FILESTREAM, который имеет тип данных varbinary(max) с включенным атрибутом FILESTREAM. Кроме того, каждая таблица с включенным FILESTREAM должна иметь столбец UNIQUEIDENTIFIER.

CREATE TABLE Tbl_Support_Documents
(
    Document_ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
    Document_Name varchar(1000),
    Document_Type varchar(50),
    DocumentBin varbinary (max) FILESTREAM NULL
)

Шаг 3: Создание хранимой процедуры для импорта файлов

Создайте хранимую процедуру, которая будет импортировать файлы из определенного каталога в таблицу с включенным FILESTREAM. Эта хранимая процедура будет использовать команду DOS для получения списка файлов в каталоге и вставки их во временные таблицы. Затем она будет перебирать файлы и вставлять их в таблицу с включенным FILESTREAM с помощью функции OPENROWSET(Bulk).

CREATE PROCEDURE ImportFiles @DirectoryLocation VARCHAR(max)
AS
BEGIN
    -- Создание временных таблиц
    CREATE TABLE #Final_Document 
    (
        id INT IDENTITY (1, 1) PRIMARY KEY,
        Document_Name VARCHAR(max),
        Document_Type VARCHAR(max),
        Directory_Location VARCHAR(50)
    )

    CREATE TABLE #Document_Name
    (
        id INT IDENTITY (1, 1) PRIMARY KEY,
        Document_Name VARCHAR(2000)
    )

    -- Получение списка файлов в каталоге
    DECLARE @SQLCommand VARCHAR(4000)
    SET @SQLCommand = 'dir "' + @DirectoryLocation + '" /A-D /B'
    INSERT INTO #Document_Name (Document_Name)
    EXECUTE xp_cmdshell @SQLCommand

    -- Вставка информации о файлах во временные таблицы
    INSERT INTO #Final_Document
    (
        [Document_Name],
        [Document_Type],
        [Directory_Location]
    )
    SELECT
        Document_Name,
        CASE
            WHEN (Document_Name LIKE '%jpg%') OR (Document_Name LIKE '%png%') OR (Document_Name LIKE '%jpg%') OR (Document_Name LIKE '%bmp%') THEN 'Изображения'
            WHEN (Document_Name LIKE '%txt%') OR (Document_Name LIKE '%rtf%') OR (Document_Name LIKE '%xls%') OR (Document_Name LIKE '%doc%') THEN 'Документы'
            ELSE 'Другие файлы'
        END AS 'Тип файла',
        @DirectoryLocation AS 'Местоположение файла'
    FROM #Document_Name

    -- Импорт файлов в таблицу с включенным FILESTREAM
    DECLARE @FileCount INT
    DECLARE @I INT = 0
    DECLARE @FileName NVARCHAR(max)
    DECLARE @SQLText NVARCHAR(max)
    DECLARE @FileLocation NVARCHAR(max)

    SET @FileCount = (SELECT COUNT(*) FROM #Final_Document)

    WHILE (@I < @FileCount)
    BEGIN
        SET @FileName = (SELECT TOP 1 Document_Name FROM #Final_Document)
        SET @FileLocation = (SELECT TOP 1 @DirectoryLocation + '\' + @FileName FROM #Final_Document)

        SET @SQLText = 'INSERT INTO Tbl_Support_Documents (Document_ID, Document_Name, Document_Type, DocumentBin)
                        SELECT NEWID(), ''' + @FileName + ''', (SELECT Document_Type FROM #Final_Document WHERE Document_Name = ''' + @FileName + '''),
                        BulkColumn FROM OPENROWSET(Bulk ''' + @FileLocation + ''', Single_Blob) AS tb'

        EXEC Sp_executesql @SQLText

        DELETE FROM #Final_Document WHERE Document_Name = @FileName

        SET @I = @I + 1
    END
END

Шаг 4: Загрузка документов и файлов

Выполните хранимую процедуру, указав местоположение каталога, где находятся файлы, в качестве параметра. Это загрузит файлы в таблицу с включенным FILESTREAM.

EXECUTE ImportFiles @DirectoryLocation = 'E:\Documents'

Шаг 5: Проверка файлов

Чтобы убедиться, что файлы успешно сохранены в контейнере FILESTREAM, вы можете получить местоположение файла с помощью метода PathName() столбца FILESTREAM.

SELECT Document_Name, DocumentBin.PathName() AS 'Местоположение файла' 
FROM Tbl_Support_Documents 
WHERE Document_Name = 'Demo_Script.sql'

Перейдя в контейнер данных FILESTREAM, вы можете подтвердить, что файлы были созданы.

Вывод

В этой статье мы рассмотрели, как импортировать несколько документов и файлов в базу данных с включенным FILESTREAM в SQL Server. Следуя описанным выше шагам, вы можете эффективно импортировать и хранить файлы в таблице с включенным FILESTREAM, что позволяет ускорить доступ к файлам и улучшить управление пространством.