我需要创建一个存储过程来从 MinIO 存储桶中读取 .CSV 文件。我需要将这些文件中的数据存储到相应的 SQL Server 表中。例如:如果文件是 na...
我需要创建一个存储过程来 .CSV
从 MinIO 存储桶中读取文件。我需要将这些文件中的数据存储到相应的 SQL Server 表中。
例如:如果文件名为 info_sec.csv
,那么我必须将数据存储到 dbo.info_sec
表中。
我们如何在 SQL Server 中实现这一点?
我尝试使用以下代码从本地驱动器获取数据
ALTER PROCEDURE [dbo].[InsertDataFromCSVDirectory]
(
@DirectoryPath NVARCHAR(1000), -- Directory path containing CSV files
@DateParam DATE -- Date parameter in 'YYYY-MM-DD' format
)
AS
BEGIN
SET NOCOUNT ON;
-- Declare variables
DECLARE @TableName NVARCHAR(100);
DECLARE @FileNamePattern NVARCHAR(1000);
DECLARE @Cmd NVARCHAR(1000);
DECLARE @BulkInsertSQL NVARCHAR(MAX);
-- Get the date suffix from the parameter
DECLARE @DateSuffix NVARCHAR(100);
SET @DateSuffix = CONVERT(NVARCHAR(10), @DateParam, 23);
-- Get a list of table names to process
DECLARE @Tables TABLE (TableName NVARCHAR(100));
INSERT INTO @Tables (TableName)
VALUES ('emp'), ('dept'), ('invoice'); -- Add more table names if needed
-- Loop through each table
DECLARE tableCursor CURSOR FOR
SELECT TableName FROM @Tables;
OPEN tableCursor;
FETCH NEXT FROM tableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build the file name pattern
SET @FileNamePattern = @TableName + '_' + @DateSuffix + '.csv';
-- Construct the full file path with the directory
DECLARE @FilePath NVARCHAR(1000);
SET @FilePath = @DirectoryPath + '\' + @FileNamePattern;
-- Check if the file exists using xp_cmdshell
SET @Cmd = 'dir "' + @FilePath + '" /b';
DECLARE @Result TABLE (Result VARCHAR(1000));
INSERT INTO @Result
EXEC xp_cmdshell @Cmd;
IF NOT EXISTS (SELECT * FROM @Result WHERE Result LIKE '%' + @FileNamePattern + '%')
BEGIN
PRINT 'File ' + @FilePath + ' not found for table ' + @TableName + '.';
END
ELSE
BEGIN
-- Construct the BULK INSERT statement
SET @BulkInsertSQL = N'
USE master;
BULK INSERT dbo.' + QUOTENAME(@TableName) + N'
FROM ''' + @FilePath + N'''
WITH (
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
FIRSTROW = 1,
KEEPNULLS
);
';
-- Execute the BULK INSERT statement
EXEC sp_executesql @BulkInsertSQL;
PRINT 'Data inserted into ' + @TableName + ' table from file ' + @FilePath + '.';
END
FETCH NEXT FROM tableCursor INTO @TableName;
END;
CLOSE tableCursor;
DEALLOCATE tableCursor;
END;