--没事,改改上面的存储过程--第一个这样改一下,我觉得容易看一点:Create procedure usp_ImportMultipleFiles @filepath varchar(500), @pattern varchar(100), @TableName varchar(128) as set quoted_identifier off declare @query varchar(8000)create table #x (name varchar(200)) set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"' insert #x exec(@query)declare tb cursor local for select sql='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+name+'" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")' from #x where name is not nullopen tb fetch next from tb into @query while @@fetch_status=0 begin exec (@query) insert into logtable (query) select @query fetch next from tb into @query end close tb deallocate tb
--再改改第二个Create procedure usp_ImportMultipleFilesBCP @servername varchar(128), @DatabaseName varchar(128), @filepath varchar(500), @pattern varchar(100), @TableName varchar(128) as declare @query varchar(8000)create table #x (name varchar(200)) set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"' insert #x exec (@query)declare tb cursor local for select sql='bcp "'+ @databasename+'.dbo.'+@Tablename + '" in "'+ @Filepath+name+'" -S' + @servername + ' -T -c -r\n -t,' from #x where name is not nullopen tb fetch next from tb into @query while @@fetch_status=0 begin exec master..xp_cmdshell @query insert into logtable (query) select @query fetch next from tb into @query end close tb deallocate tb
@filepath varchar(500),
@pattern varchar(100),
@TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(8000)create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec(@query)declare tb cursor local for
select sql='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+name+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
from #x where name is not nullopen tb
fetch next from tb into @query
while @@fetch_status=0
begin
exec (@query)
insert into logtable (query) select @query
fetch next from tb into @query
end
close tb
deallocate tb
@servername varchar(128),
@DatabaseName varchar(128),
@filepath varchar(500),
@pattern varchar(100),
@TableName varchar(128)
as
declare @query varchar(8000)create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)declare tb cursor local for
select sql='bcp "'+ @databasename+'.dbo.'+@Tablename + '"
in "'+ @Filepath+name+'" -S' + @servername + ' -T -c -r\n -t,'
from #x where name is not nullopen tb
fetch next from tb into @query
while @@fetch_status=0
begin
exec master..xp_cmdshell @query
insert into logtable (query) select @query
fetch next from tb into @query
end
close tb
deallocate tb