读取文件目录，然后将文件输入SQL表日志，而仅在新文件中

Question

im试图使用xp_cmdshell读取文件目录，然后将这些文件名粘贴到日志表中。每次阅读目录时，我都只想将新文件添加到日志表中。以下是我现在拥有的，但它凌乱。

Declare @FileName Table(FileList Varchar(255))
Insert Into @FileName  
EXEC XP_CMDSHELL 'dir "C:Data LogEODFiles*.CSV" /b'
Declare @FilePath Varchar(255)
Select Top 1 FilePath= FileList 
FROM [dbo].[EOD_Log] 
right join  @FileName 
on filelist = File_log
where filelist is NOT NULL  and File_log is NULL 
Insert Into  [dbo].[EOD_Log] (File_log)
select @filepath from @FileName where @filepath is not NULL 

Answer 1

DECLARE @FileName Table(FileList Varchar(255))
INSERT INTO @FileName  
EXEC XP_CMDSHELL 'dir "C:Data LogEODFiles*.CSV" /b'
INSERT INTO  [dbo].[EOD_Log] (filename)
SELECT FileList From @FileName 
WHERE Filelist NOT IN (select * FROM [dbo].[EOD_Log])  
ORDER BY filelist desc

这应该解决您的空问题，而不是在分配中没有行时要空置的变量，而是简单地不会插入。为什么还要使用前1名？而且我认为您甚至不需要检查Filelist列中的空。

Answer 2

我让它起作用，但仍然凌乱。

DECLARE @FileName Table(FileList Varchar(255))
INSERT INTO @FileName  
EXEC XP_CMDSHELL 'dir "C:Data LogEODFiles*.CSV" /b'
DECLARE @FilePath Varchar(255)
SELECT Top 1 @FilePath= FileList From @FileName 
WHERE FileList is Not Null and Filelist NOT IN (select * FROM [dbo].[EOD_Log])  
ORDER BY filelist desc
INSERT INTO  [dbo].[EOD_Log] (filename)
VALUES (@filepath)
DELETE from [dbo].[EOD_Log]
WHERE filename IS NULL