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.

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 MSSQL 批量读取文件夹中文件（mssql 读文件夹）