declare @tb table(id int identity,filename nvarchar(1000)) insert @tb(filename) exec xp_cmdshell 'dir G:\*.txt /b' delete @tb WHERE filename is null;declare cur cursor for select filename from @tb; declare @filename nvarchar(1000);open cur; fetch next from cur into @filename; while @@fetch_status=0 begin set @filename = 'G:\' + @filename --exec sp_name @filename --这里执行你的存储过程 fetch next from cur into @filename; end close cur; deallocate cur;
或者使用clr存储过程找到所有txt文件..然后循环这些文件..导入.
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
public partial class StoredProcedures
{
[SqlProcedure(Name="GetDirectoryFiles")]
public static void GetDirectoryFiles(SqlString path,SqlString pattern,SqlBoolean recursive)
{
SqlPipe pipe = SqlContext.Pipe;
string dir = path.Value.ToString(); if (Directory.Exists(dir) == false)
{
pipe.Send("该目录不存在");
return;
} SearchOption searchOption;
if (recursive == true)
{
searchOption = SearchOption.AllDirectories;
}
else
{
searchOption = SearchOption.TopDirectoryOnly;
}
SqlMetaData[] metaData = new SqlMetaData[4];
metaData[0] = new SqlMetaData("FILE_NAME", SqlDbType.NVarChar, 1024);
metaData[1] = new SqlMetaData("CREATED_TIME", SqlDbType.DateTime);
metaData[2] = new SqlMetaData("ATTRIBUTES", SqlDbType.NVarChar, 500);
metaData[3] = new SqlMetaData("SIZE(KB)", SqlDbType.Int); DirectoryInfo dirInfo = new DirectoryInfo(dir); FileInfo[] files = dirInfo.GetFiles(pattern.Value.ToString(), searchOption);
if (files.Length > 0)
{
SqlDataRecord record = new SqlDataRecord(metaData);
pipe.SendResultsStart(record);
foreach (FileInfo file in files)
{
string fullpath = file.FullName;
DateTime createtime = file.CreationTime; FileAttributes fileAttributes = file.Attributes;
string attributes = ""; if ((fileAttributes | FileAttributes.ReadOnly) == FileAttributes.ReadOnly)
{
attributes += "ReadOnly\t";
}
if ((fileAttributes | FileAttributes.Archive) == FileAttributes.Archive)
{
attributes += "Archive\t";
}
if ((fileAttributes | FileAttributes.Hidden) == FileAttributes.Hidden)
{
attributes += "Hidden\t";
} int filesize = (int)file.Length / 1024;
record.SetSqlString(0, new SqlString(fullpath));
record.SetSqlDateTime(1, new SqlDateTime(createtime));
record.SetSqlString(2, new SqlString(attributes));
record.SetSqlInt32(3, new SqlInt32(filesize)); pipe.SendResultsRow(record);
}
pipe.SendResultsEnd();
}
else
{
pipe.Send("没找到文件");
}
}
};----------------------------------------------------写个clr存储过程.然后在sql server里使用这个程序集..
CREATE ASSEMBLY Ass_DirectoryTools
FROM 'x:\dllname.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS;
GOCREATE PROC dbo.sp_GetDirectoryFiles
@path nvarchar(1024),
@pattern nvarchar(10),
@recursive bit
AS
EXTERNAL Ass_DirectoryTools.StoredProcedures.GetDirectoryFiles
GO
attributes nvarchar(1000),size int);EXEC #T EXEC dbo.sp_GetDirectoryFiles 'c:\','*.txt',1;然后循环这个表..得到文件路径..然后动态SQL就可以了.
insert @tb(filename) exec xp_cmdshell 'dir G:\*.txt /b'
delete @tb WHERE filename is null;declare cur cursor for
select filename from @tb;
declare @filename nvarchar(1000);open cur;
fetch next from cur into @filename;
while @@fetch_status=0
begin
set @filename = 'G:\' + @filename
--exec sp_name @filename --这里执行你的存储过程
fetch next from cur into @filename;
end
close cur;
deallocate cur;