IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp;
-- Создаю временную таблицу #csv_temp
CREATE TABLE #csv_temp (
              [Times] VARCHAR(100),
	        [Caller_Name] VARCHAR (200),
			[Caller_Number] VARCHAR (200),
			[Callee_Name] VARCHAR (200),
			[Callee_Numbers] VARCHAR(100),
			[DOD] VARCHAR(100),
			[DID] VARCHAR(100),
			[Call_Duration_(s)] VARCHAR (200),
			[Talk_Duration_(s)] VARCHAR (200),
			[Status] VARCHAR(100),
			[Source_Trunk] VARCHAR(50),
			[Destination Trunk] VARCHAR(100),
			[Communication_Type] VARCHAR(100),
			[PIN_Code] VARCHAR(10),
			[Caller IP Address] VARCHAR(200),
			[Cost] VARCHAR(100),
			[Billing_Account] VARCHAR(100)
			
)
-- Импортировать во временную таблицу информацию из файла csv

-- BULK INSERT #csv_temp
-- FROM 'C:\serg\all.csv'
-- WITH (firstrow = 2,fieldterminator = ';', rowterminator = '0x0a',CODEPAGE = '1251');
-- переменные 
declare @filename varchar(255),
        @path     varchar(255),
        @sql      varchar(8000),
		@filename varchar(255)
--получить список файлов для обработки:
SET @path = 'C:\serg\'

--цикл курсора
declare c1 CURSOR LOCAL  READ_ONLY  FAST_FORWARD  FOR
 SELECT TIMES,Caller_Name FROM @filename where  [Times]  like '%.csv%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
  begin
  --bulk insert won't take a variable name, so make a sql and execute it instead:
   set @sql = 'BULK INSERT #csv_temp FROM ''' + @path + @filename + ''' '
       + '     WITH ( 
               FIELDTERMINATOR = '','', 
               ROWTERMINATOR = ''\n'', 
               FIRSTROW = 2 
            ) '
print @sql
exec (@sql)

  fetch next from c1 into @path,@filename
  end
close c1
deallocate c1



select * from #csv_temp

set nocount off;

declare @files table ([filename] varchar(255), sub smallint, isfile smallint)
declare @sql varchar(2048)
declare @filename varchar(255) 
declare @path varchar(255) = 'c:\serg'


insert into @files ([filename],sub,isfile)
exec xp_dirtree @path,1,1

delete @files where [filename] not like '%.csv' 
--select * from @files 

while exists (select top 1 1 from @files)
begin
	select top 1 @filename=filename from @files 

	/*import file*/
	--print 'Import file: '+ @path+'\'+@filename  

	set @sql = 'BULK INSERT #csv_temp FROM ''' + @path +'\'+ @filename + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', FIRSTROW = 2 ) '
	print @sql
	exec (@sql)

	delete @files where [filename]=@filename 
	set @filename=''
end

 

IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp;
-- Создаю временную таблицу #csv_temp
CREATE TABLE #csv_temp (
            [Times] VARCHAR(250),
	        [Caller_Name] VARCHAR (250),
			[Caller_Number] VARCHAR (250),
			[Callee_Name] VARCHAR (250),
			[Callee_Numbers] VARCHAR(250),
			[DOD] VARCHAR(250),
			[DID] VARCHAR(250),
			[Call_Duration_(s)] VARCHAR (200),
			[Talk_Duration_(s)] VARCHAR (200),
			[Status] VARCHAR(200),
			[Source_Trunk] VARCHAR(50),
			[Destination Trunk] VARCHAR(100),
			[Communication_Type] VARCHAR(100),
			[PIN_Code] VARCHAR(10),
			[Caller IP Address] VARCHAR(200),
			[Cost] VARCHAR(100),
			[Billing_Account] VARCHAR(100)
			
)

set nocount off;

declare @files table ([filename] varchar(255), sub smallint, isfile smallint)
declare @sql varchar(2048)
declare @filename varchar(255) 
declare @path varchar(255) = 'c:\serg'


insert into @files ([filename],sub,isfile)
exec xp_dirtree @path,1,1

delete @files where [filename] not like '%.csv' 
--select * from @files 

while exists (select top 1 1 from @files)
begin
	select top 1 @filename=filename from @files 

	/*import file*/
	--print 'Import file: '+ @path+'\'+@filename  

	set @sql = 'BULK INSERT #csv_temp FROM ''' + @path +'\'+ @filename + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', FIRSTROW = 2 ) '
	print @sql
	exec (@sql)

	delete @files where [filename]=@filename 
	set @filename=''
end