使用Cursor执行后，Names数据库未显示插入的表与数据

我编写了如下SQL存储过程，执行过程无报错，但在Names数据库中无法看到预期的表及本该批量插入的数据：

• 查询语句：用于检查Names数据库中是否存在目标表的查询
• 待插入数据：目标路径下存在多个以yob开头的TXT文件，内容为逗号分隔的姓名、性别、计数数据

原存储过程代码：

CREATE PROCEDURE import_txt_files12
AS
BEGIN
  DECLARE @file varchar(255)
  DECLARE @path varchar(255)
  DECLARE @sql varchar(8000)
  DECLARE @tablename varchar(255)

  SET @path = 'C:\Users\marti\OneDrive\Desktop\Data Analist course\Datasets\names\'

  DECLARE file_cursor CURSOR FOR
    SELECT physical_name FROM sys.master_files
    WHERE [name] LIKE 'yob%' AND physical_name LIKE @path + '%'

  OPEN file_cursor

  FETCH NEXT FROM file_cursor INTO @file

  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- Extract table name from file name
    SET @tablename = SUBSTRING(@file, LEN(@path), LEN(@file) - LEN(@path) - 4)

    SET @sql = 'BULK INSERT [Names].[dbo].[' + @tablename + '] FROM ''' + @file + ''' WITH (FORMAT = ''txt'', FIRSTROW = 1, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'
    EXEC (@sql)

    FETCH NEXT FROM file_cursor INTO @file
  END

  CLOSE file_cursor
  DEALLOCATE file_cursor
END

核心问题分析 #

1. 数据源错误：sys.master_files是SQL Server存储数据库物理文件的系统视图，无法读取本地TXT文件，游标根本没查到任何目标文件，循环体从未执行。
2. 缺少建表逻辑：BULK INSERT仅能向已存在的表插入数据，原代码未创建目标表，即使查到文件也会报错（但原代码因为没查到文件所以没触发错误）。
3. 表名提取逻辑不可靠：原SUBSTRING计算依赖路径长度固定，若路径有变动会导致表名提取错误。

修正方案 #

1. 修正数据源：读取指定路径下的TXT文件 #

使用xp_cmdshell获取指定路径下的yob开头TXT文件列表（需先开启该功能）：

-- 开启xp_cmdshell（仅需执行一次）
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

2. 添加自动建表逻辑 #

在批量插入前，检查表是否存在，不存在则根据数据结构创建：

IF NOT EXISTS (SELECT * FROM [Names].sys.tables WHERE name = @tablename AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    SET @sql = 'CREATE TABLE [Names].[dbo].[' + @tablename + '] (
        Name VARCHAR(100),
        Gender CHAR(1),
        Count INT
    )';
    EXEC (@sql);
END

3. 优化表名提取逻辑 #

改用更可靠的字符串处理方式提取表名：

-- 拼接完整文件路径
SET @file = @path + @file;
-- 提取表名（去掉路径和.txt后缀）
SET @tablename = REPLACE(@file, @path, '');
SET @tablename = LEFT(@tablename, LEN(@tablename) - 4);

完整修正后的存储过程 #

CREATE PROCEDURE import_txt_files12
AS
BEGIN
    DECLARE @file varchar(255)
    DECLARE @path varchar(255)
    DECLARE @sql varchar(8000)
    DECLARE @tablename varchar(255)

    SET @path = 'C:\Users\marti\OneDrive\Desktop\Data Analist course\Datasets\names\'

    -- 自动开启xp_cmdshell（若未开启）
    IF (SELECT value FROM sys.configurations WHERE name = 'xp_cmdshell') = 0
    BEGIN
        EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
    END

    -- 获取指定路径下的yob开头TXT文件列表
    DECLARE file_cursor CURSOR FOR
        SELECT [output]
        FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
                        'EXEC master.dbo.xp_cmdshell ''dir "' + @path + 'yob*.txt" /b''')
        WHERE [output] IS NOT NULL;

    OPEN file_cursor
    FETCH NEXT FROM file_cursor INTO @file

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 拼接完整文件路径
        SET @file = @path + @file;
        -- 提取表名（去掉路径和.txt后缀）
        SET @tablename = REPLACE(@file, @path, '');
        SET @tablename = LEFT(@tablename, LEN(@tablename) - 4);

        -- 自动创建表（若不存在）
        IF NOT EXISTS (SELECT * FROM [Names].sys.tables WHERE name = @tablename AND schema_id = SCHEMA_ID('dbo'))
        BEGIN
            SET @sql = 'CREATE TABLE [Names].[dbo].[' + @tablename + '] (
                Name VARCHAR(100),
                Gender CHAR(1),
                Count INT
            )';
            EXEC (@sql);
        END

        -- 执行批量插入，转义单引号避免语法错误
        SET @sql = 'BULK INSERT [Names].[dbo].[' + @tablename + '] 
                    FROM ''' + REPLACE(@file, '''', '''''') + ''' 
                    WITH (
                        FIRSTROW = 1,
                        FIELDTERMINATOR = '','',
                        ROWTERMINATOR = ''\n'',
                        CODEPAGE = ''ACP''
                    )';
        -- 可选：打印SQL语句用于调试
        -- PRINT @sql;
        EXEC (@sql);

        FETCH NEXT FROM file_cursor INTO @file
    END

    CLOSE file_cursor
    DEALLOCATE file_cursor

    -- 可选：执行完毕后关闭xp_cmdshell
    -- EXEC sp_configure 'xp_cmdshell', 0;
    -- RECONFIGURE;
    -- EXEC sp_configure 'show advanced options', 0;
    -- RECONFIGURE;
END

额外注意事项 #

• 确保SQL Server服务账户有目标路径的读写权限，OneDrive路径可能存在权限限制，建议先将文件复制到本地普通目录测试。
• 执行存储过程时，确保当前会话默认数据库为Names，或所有对象引用均明确指定[Names]数据库。
• 可开启PRINT @sql语句查看生成的SQL，便于排查语法或路径错误。

内容的提问来源于stack exchange，提问作者Jose G Martinez