如何通过SQL存储过程用固定路径导入文件名可变的文件？

Got it, let's sort this out so you never have to tweak your stored procedure just because a filename changes. There are two solid approaches here—one lets you specify which file to import when you run the proc, and the other automatically grabs the latest file in your target folder.

1. Pass the filename as a parameter (manual control) #

The simplest fix is to modify your stored procedure to accept the full file path as an input parameter, then use dynamic SQL to build the import command. This way, you just pass the new filename when you call the proc instead of editing its code.

Here's an example for SQL Server using BULK INSERT (adjust the WITH clause to match your file's format):

CREATE PROCEDURE dbo.ImportTextFile
    @FullFilePath NVARCHAR(500), -- e.g., 'C:\YourImportFolder\new_data_20240520.txt'
    @TargetTableName NVARCHAR(128) = 'YourDestinationTable' -- Optional: default to your table
AS
BEGIN
    SET NOCOUNT ON;

    -- Build dynamic SQL to avoid hardcoding the filename
    DECLARE @ImportSQL NVARCHAR(MAX);
    SET @ImportSQL = N'BULK INSERT ' + QUOTENAME(@TargetTableName) + N'
                      FROM ''' + REPLACE(@FullFilePath, '''', '''''') + N'''
                      WITH (
                          FIELDTERMINATOR = '','', -- Change to your file's delimiter
                          ROWTERMINATOR = ''\n'', -- Adjust line ending if needed
                          FIRSTROW = 2 -- Use this if your file has a header row
                      )';

    -- Execute the dynamic command
    EXEC sp_executesql @ImportSQL;
END

To use it, just call the proc with the new file path:

EXEC dbo.ImportTextFile @FullFilePath = 'C:\YourImportFolder\latest_file.txt';

Pro tip: The REPLACE call escapes single quotes in the file path to prevent SQL injection, and QUOTENAME handles any weird characters in your table name.

2. Automatically import the latest file in the folder #

If you don't want to specify the filename every time, you can add logic to the proc that finds the most recently modified file in your target folder. We'll use xp_cmdshell here (note: you'll need to enable it first, so check your security policies):

CREATE PROCEDURE dbo.ImportLatestTextFile
    @FolderPath NVARCHAR(500), -- e.g., 'C:\YourImportFolder\'
    @TargetTableName NVARCHAR(128) = 'YourDestinationTable'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LatestFileName NVARCHAR(255);
    DECLARE @FullFilePath NVARCHAR(500);
    DECLARE @ImportSQL NVARCHAR(MAX);

    -- Temp table to store file list
    CREATE TABLE #FileDetails (
        FileName NVARCHAR(255),
        FileModifiedDate DATETIME
    );

    -- Enable xp_cmdshell (only if it's not already enabled)
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;

    -- Get list of .txt files sorted by modified date (newest first)
    INSERT INTO #FileDetails (FileName, FileModifiedDate)
    EXEC xp_cmdshell 'dir "' + @FolderPath + '*.txt" /b /o-d';

    -- Clean up empty rows from xp_cmdshell output
    DELETE FROM #FileDetails WHERE FileName IS NULL OR FileModifiedDate IS NULL;

    -- Grab the latest filename
    SELECT TOP 1 @LatestFileName = FileName FROM #FileDetails;

    IF @LatestFileName IS NOT NULL
    BEGIN
        SET @FullFilePath = @FolderPath + @LatestFileName;
        -- Build and run the import command
        SET @ImportSQL = N'BULK INSERT ' + QUOTENAME(@TargetTableName) + N'
                          FROM ''' + REPLACE(@FullFilePath, '''', '''''') + N'''
                          WITH (
                              FIELDTERMINATOR = '','',
                              ROWTERMINATOR = ''\n'',
                              FIRSTROW = 2
                          )';

        EXEC sp_executesql @ImportSQL;
        PRINT 'Successfully imported: ' + @FullFilePath;
    END
    ELSE
    BEGIN
        PRINT 'No .txt files found in the specified folder.';
    END

    -- Disable xp_cmdshell (optional, based on your security rules)
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE;
    EXEC sp_configure 'show advanced options', 0;
    RECONFIGURE;

    DROP TABLE #FileDetails;
END

Call it like this, and it'll handle the rest:

EXEC dbo.ImportLatestTextFile @FolderPath = 'C:\YourImportFolder\';

Quick Notes: #

• Make sure the SQL Server service account has read permissions to the target folder—otherwise, it won't be able to access the files.
• Adjust the WITH clause parameters (delimiters, FIRSTROW, etc.) to match your text file's structure.
• If you're using a different database (like MySQL), the core idea is the same—use dynamic SQL with a parameter or auto-discover the latest file, then use LOAD DATA INFILE instead of BULK INSERT.

内容的提问来源于stack exchange，提问作者akshay gate