只插入某些.csv文件

Question

我正在研究Server 2008 R2。

我只需要批量插入一个特定的.CSV文件。

这是我的剧本：

BULK INSERT HPCM_HRSS.dbo.HRSS_TEST
        FROM '\\FOLDER\HRSS TEST\BISM043_Oracle FRG Workforce 201410.csv'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n',
                FIRSTROW = 2
    )
GO

只有一个文件具有这个命名，我在导入之后存档这个文件。文件名从“BISM043_Oracle FRGWorks201410.csv”更改为“BISM043_Oracle FRGWorks201411.csv”。

如何更改脚本，以便在该特定文件夹中导入任何命名为“BISM043_Oracle FRG”的文件？

如有任何建议，将不胜感激。

Answer 1

谢谢你，罗斯·普雷瑟，你的回答确实把我引向了正确的方向。

以下是我为了得到正确的结果所做的事情：

DECLARE @SQL        nvarchar(max),
        @FileName   nvarchar(200)

        CREATE TABLE #Temp_FileName
        (
            [FileName] nvarchar(200)
        )

INSERT INTO #Temp_FileName
EXECUTE XP_CMDSHELL 'dir \\FAS-RBGFS01\costec\HRSS\ /b'

DELETE FROM #Temp_FileName
WHERE [FileName] NOT LIKE 'BISM043_Oracle FRG Workforce%'


SELECT TOP(1) @FileName = [FileName] FROM #Temp_FileName ORDER BY [FileName] DESC

SET @SQL = 'BULK INSERT HPCM_HRSS.dbo.HRSS_TEST
        FROM ''\\FAS-RBGFS01\costec\HRSS\' + @FileName + '''
            WITH
    (
                FIELDTERMINATOR = '','',
                ROWTERMINATOR = ''\n'',
                FIRSTROW = 2
    )'

EXEC (@SQL)

DROP TABLE #Temp_FileName

Answer 2

来放大SQLCMD模式。下面是一个CMD批处理文件，它定位该文件，然后调用SQLCMD：

echo on
setlocal enabledelayedexpansion
rem ---
rem this finds the latest .bak file in the backup directory
rem ---
set SQLBACKUPPATH=\\SERVER\sqlbackups\SQLSERVERNAME\DBNAME
for /F %%f in ('dir /b /o:-d !SQLBACKUPPATH!\*.bak') do set BAKFILE=!SQLBACKUPPATH!\%%f  

rem ---
rem this passes the found bak file as the parameter BAKFILE to the SQL file in SQLCMD mode
(sqlcmd -v BAKFILE ="!BAKFILE!" -i UpdateDevDBFromProdBak.sql -I -S SQLSERVER -E  -d master )

下面是最后一行中调用的UpdateDevDBFromProdBak.SQL文件：

ALTER DATABASE [MyDB]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

RESTORE DATABASE [MyDB] FROM DISK = '$(BAKFILE)'WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5;
GO

ALTER DATABASE [MyDB] SET  RECOVERY SIMPLE
GO

ALTER DATABASE [MyDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

USE [MyDB]

EXEC sp_change_users_login 'auto_fix','mydbuser'
GO