Have you ever wondered how to efficiently import and export binary files using SQL Server? In this article, we will explore a method that can help you achieve this task.

Preparations

First, we need to create a table to store the file paths. We can use a VARCHAR column to store the absolute path of the binary files. The size of the VARCHAR column depends on the depth of your directory structure and the length of the file names.

CREATE TABLE files (
    filepath VARCHAR(512)
);

Next, we can use the BULK INSERT statement to load the file paths from a text file into the table. The text file should contain the absolute paths of the binary files, with each path on a new line.

BULK INSERT files
FROM 'd:\out.txt'
WITH (
    rowterminator='\n',
    codepage=1251
);

It’s important to note that if your file names contain characters from a specific character set, you may need to specify the appropriate codepage in the BULK INSERT statement.

Loading the Binary Files

Now that we have the file paths stored in the “files” table, we can proceed to load the binary files into another table. Before performing the bulk loading operation, it is recommended to alter the database’s recovery model to SIMPLE or BULK_LOGGED, if possible, to improve performance.

CREATE TABLE bindata (
    data VARBINARY(MAX)
);

DECLARE @filename VARCHAR(512),
        @sql VARCHAR(512);

DECLARE a_cur CURSOR STATIC FOR
    SELECT filepath FROM files;

OPEN a_cur;
FETCH NEXT FROM a_cur INTO @filename;

WHILE (@@fetch_status=0)
BEGIN
    SET @sql = ' 
        INSERT INTO bindata(data) 
        SELECT * FROM 
        OPENROWSET(BULK ''' + @filename + ''', SINGLE_BLOB) AS f;';

    EXEC (@sql);
    FETCH NEXT FROM a_cur INTO @filename;
END;

CLOSE a_cur;
DEALLOCATE a_cur;

The loading process may vary depending on the number and size of the binary files you want to load. Once the loading is completed, the binary data will be stored in the “bindata” table.

Exporting the Binary Files

To export the binary files from the database, you can use the following script. Before executing the script, make sure to enable the xp_cmdshell extended stored procedure.

EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

Now, you can use the following procedure to export the binary files:

DECLARE @a INT,
        @i INT,
        @filename VARCHAR(300),
        @cmd VARCHAR(500);

SET @i = 0;
SELECT @a = MAX(file_id) FROM bindata;

WHILE @i != @a
BEGIN                             
    SET @filename = 'd:\tmp\' + CAST(@i AS VARCHAR) + '.dat';
    SET @cmd = 'bcp "SELECT data FROM db1.dbo.bindata WHERE file_id=' + CAST(@i+1 AS VARCHAR) + '" queryout ' + @filename + ' -T -N';
    EXEC master..xp_cmdshell @cmd;
    SET @i = @i + 1;
END;

This script will export each binary file from the “bindata” table to the specified directory.

Dealing with File Deltas

If you need to handle the delta between the previous load into the database and the current files that appeared in the same directories, there are a couple of methods you can use.

Method 1: Dump the existing file paths from the database using the BCP utility. Then, create a new list of file paths using the find utility. Finally, use the diff utility to compare the two lists and extract the delta.

Method 2: Upload the whole newly created text file with paths again without any constraint checking. Before the upload, remember or write down the current maximum file_id from the “files” table. You can then find the duplicates and eliminate them based on the file_id.

Conclusion

In this article, we have explored a method for importing and exporting binary files in SQL Server. While this method may not be the fastest or most efficient, it can serve as a starting point for your own implementation. SQL Server provides various tools and techniques that can be used to accomplish this task, and it’s up to you to find the approach that best suits your needs.