sql-server – 如何通过SQL Server的xp_cmdshell在Windows命令shell中根据创建日

在SQL Server中,我使用下面的查询将特定目录(例如z :)中的所有“.jpg”文件名加载到表中.

我想知道是否有办法在Windows命令提示符下根据创建日期而不是修改日期加载文件.以下查询仅在执行xp_cmdshell时使用修改日期.

-- Create the table to store file list
CREATE TABLE myFilesTable (myFileID INT IDENTITY,myFileName NVARCHAR(256))

-- Insert file list from directory to SQL Server
DECLARE @Command varchar(1024) = 'z: & forfiles /m *.jpg /s /d 07/16/2015 /c "cmd /c echo @fdate @ftime @path"'

INSERT INTO myFilesTable
   EXEC MASTER.dbo.xp_cmdshell @Command

-- Check the list
SELECT * FROM myFilesTable
GO

2015年7月16日变量@Command是修改日期.显然,命令forfiles没有通过Created Date过滤文件的线索.

以下是上面给出的查询的一些结果,其中FileNames以修改日期作为前缀.

myFileID | myFileName
----------------------
1        | NULL
2        | 8/18/2015 11:13:08 AM "Z:LDB1 App ExportTop Star_Aluminium Frames & Furniture (B)-31267.jpg"
3        | 8/19/2015 5:44:41 PM "Z:LDB2 App ExportSoe Tint_Hardware Merchants & Ironmongers-31435.jpg"
4        | 8/19/2015 10:37:13 AM "Z:Cover App ExportTaw Win Tun_Electrical Goods Sales & Repairing (A) -31382.jpg"
5        | 8/24/2015 10:34:33 AM "Z:CP1 App ExportThiri May_Fabric Shop (B)-30646.jpg"
6        | 8/17/2015 10:08:39 AM "Z:CP2 App ExportKo Tin Aung_Building Materials (B)-31300.jpg"

我也尝试过使用dir命令和timefield / t：c(创建时间)之类的东西

EXEC MASTER.dbo.xp_cmdshell 'dir z: *.jpg /t:c /s'

它给了我创建日期,但它向我显示了以下不符合预期的结果.我希望文件名具有完整路径/目录名称,如上一个结果所示.

myFileID | myFileName
----------------------
1        |  Volume in drive Z is Publication
2        |  Volume Serial Number is 3EF0-5CE4
3        | NULL
4        |  Directory of Z:
5        | NULL
6        | 07/28/2015  06:41 PM    <DIR>          .
7        | 07/28/2015  07:06 PM    <DIR>          ..
8        | 03/05/2015  11:42 AM    <DIR>          LDB1 App Export
9        | 03/05/2015  05:31 PM    <DIR>          LDB2 App Export
10       |         0 File(s)              0 bytes
11       | NULL
12       |  Directory of Z:LDB1 App Export
13       | NULL
14       | 03/05/2015  11:42 AM    <DIR>          .
15       | 07/28/2015  06:41 PM    <DIR>          ..
16       | 07/28/2015  06:49 PM         2,981,526 Kyaw Phay_Dental Equipment (A)-30998.jpg
17       | 08/31/2015  03:10 PM         3,126,629 Venus_Fashion Shops-31438.jpg
18       | 07/28/2015  06:49 PM         3,544,247 Marvellous_Tourism Services-30986.jpg
...      | ...

预期结果应如下所示,

myFileID | CreatedDate           | myFileName
----------------------------------------------
1        | 8/10/2015 11:24:16 AM | "Z:LDB1 App ExportTop Star_Aluminium Frames & Furniture (B)-31267.jpg"
2        | 8/10/2015 11:24:27 AM | "Z:LDB2 App ExportSoe Tint_Hardware Merchants & Ironmongers-31435.jpg"
3        | 8/12/2015 10:05:22 AM | "Z:Cover App ExportTaw Win Tun_Electrical Goods Sales & Repairing (A) -31382.jpg"
4        | 8/12/2015 10:05:22 AM | "Z:CP1 App ExportThiri May_Fabric Shop (B)-30646.jpg"
5        | 8/12/2015 10:05:22 AM | "Z:CP2 App ExportKo Tin Aung_Building Materials (B)-31300.jpg"

任何帮助将非常感激:)

解决方法

--Create the table to store file list
CREATE TABLE myFilesTable (myFileID INT IDENTITY,myFileCreateDate datetime,myFileName NVARCHAR(256))

--Create temporary table to store output of DIR command
CREATE TABLE #DirectoryOutput (LineID INT IDENTITY,LineData NVARCHAR(256))

--Insert file list from directory to SQL Server
DECLARE @Command varchar(1024) = 'dir z: *.jpg /t:c /s'

INSERT INTO #DirectoryOutput
   EXEC MASTER.dbo.xp_cmdshell @Command

--Check the list
insert into myFilesTable
select 
    convert(Datetime,(left(LineData,20))) CreateDate,FilePath2.FilePath + '' + right(LineData,len(LineData)-39) Filename
from #DirectoryOutput
cross apply
    (
    select Max(LineID) LineID
    from #DirectoryOutput FilePaths
    where LEFT(LineData,14)=' Directory of '
        and FilePaths.LineID < #DirectoryOutput.LineID
    ) FilePath1
join
    (
    select LineID,RIGHT(LineData,LEN(LineData)-14) FilePath
    from #DirectoryOutput FilePaths
    where LEFT(LineData,14)=' Directory of '
    ) FilePath2
on FilePath1.LineID = FilePath2.LineID
where ISDATE(left(LineData,20))=1
order by 1

select * from myFilesTable

GO

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!