copy *. ttl.txt

ALTER FUNCTION [dbo].[uf_BulkInsFlatFileList] 
(
	@tbl_in NVarChar(100), 
	@flatfilename NVarChar(200),
	@flatfiledir NVarChar(300)
)
RETURNS NVarChar(1000)
AS
BEGIN
--SET NOCOUNT ON;
DECLARE @strSQLStatement NVarChar(1000);
DECLARE @strFullName NVarChar(800);
Set @strFullName=@flatfiledir+@flatfilename;
SET @strSQLStatement=
'BULK INSERT ' + @tbl_in +
' FROM ' +''''+@strFullName+''''+
' WITH (FIELDTERMINATOR ='';'' ,FIRSTROW =1)';
RETURN  @strSQLStatement;  
END

Alter PROCEDURE p_Bulk 
(
	@strtbl_in NVarChar(100)--, 
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE 
@strQuery NVarChar(1000),
@Path VARCHAR(100), --путь к файлу
@MyFlatFileName NVarChar(200); -- его имя

SET @Path = 'f:\MyDir\';
-- получение списка файлов 
IF OBJECT_ID('TEMPDB..##dirtree') IS NOT NULL DROP TABLE ##dirtree
CREATE TABLE ##dirtree(ID int NOT NULL identity(1,1) primary key, subdirectory VARCHAR(300), depth INT, isfile INT)
INSERT INTO ##dirtree (subdirectory,depth,isfile) EXEC xp_dirtree @Path, 0, 1

-- в курсор список имен файлов
DECLARE c_FileList CURSOR FAST_FORWARD
FOR SELECT  subdirectory FROM ##dirtree WHERE isfile=1;
OPEN c_FileList;
FETCH c_FileList INTO @MyFlatFileName;
WHILE @@Fetch_Status = 0 
BEGIN
print  dbo.uf_BulkInsFlatFileList
(
@strtbl_in,
@MyFlatFileName,
@Path
);
SET @strQuery=dbo.uf_BulkInsFlatFileList
(
@strtbl_in,
@MyFlatFileName,
@Path
);		
exec sp_executesql @strQuery;
FETCH c_FileList INTO @MyFlatFileName;
END
CLOSE c_FileList;
DEALLOCATE c_FileList;    
END
GO