我需要创建一个存储过程来从 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;