MSSQL 批量读取文件夹中文件（mssql 读文件夹）

SQL Server Batch Read Files from Folder

SQL Server is a powerful relational database management system (RDBMS) developed by Microsoft that allows us to store, process and access data quickly and securely. One of its useful features, as far as data management is concerned, is the capability to manipulate folders and files from within T-SQL, which includes the ability to batch read multiple files from a folder.

Let’s say we want to read .csv files from a folder, e.g. C:\Files, then we can define the folder path and file type as follows:

DECLARE @Path varchar(512) = ‘C:\Files\’,

@FileType varchar(50) = ‘*.csv’;

To read and output each of the .csv files, we can use the xp_fileexist system stored procedure and a WHILE loop, and the T-SQL code will look something like this:

— Create a table variable to hole the file path

CREATE TABLE #Files(path varchar(512));

— Set an iterator to move through all file paths

DECLARE @Iterator INT = 1;

— Use the system stored procedure to search the defined folder path

— and output the files to the table variable

INSERT INTO #Files (path) EXEC master.dbo.xp_fileexist @Path + @FileType;

— Iterate through each file

WHILE (@Iterator

BEGIN

— Define the single file path

DECLARE @File varchar(512) = (SELECT path FROM #Files WHERE ID = @Iterator);

— Output the current file path

PRINT @File

— Open the file and output it as a T-SQL table

EXEC xp_cmdshell ‘TYPE ‘ + @File;

— Increase the iterator to move to the next file path

SET @Iterator = @Iterator + 1;

END

Finally, we can clean up after ourselves and drop the table variable:

— Drop the table variable

DROP TABLE #Files;

To summarise, SQL Server provides a powerful tool to read multiple files from a folder in a batch process. Using the xp_fileexist system stored procedure, the WHILE loop and the xp_cmdshell extended stored procedure, we can open, read and output the content of any kind of file using T-SQL.