SQL Server

Используется BULK INSERT

IF (OBJECT_ID('tempdb..#CSV_Import') IS NOT NULL) DROP TABLE #CSV_Import
IF (OBJECT_ID('tempdb..#Import') IS NOT NULL) DROP TABLE #Import
IF (OBJECT_ID('tempdb..#vendor') IS NOT NULL) DROP TABLE #vendor
IF (OBJECT_ID('tempdb..#t1') IS NOT NULL) DROP TABLE #t1
IF (OBJECT_ID('tempdb..#t2') IS NOT NULL) DROP TABLE #t2
IF (OBJECT_ID('tempdb..#t3') IS NOT NULL) DROP TABLE #t3


declare @LenVendor varchar(30)
declare @vendor varchar(30)
declare @name varchar(max)

declare @i int
declare @res varchar(250)

declare @alter_table nvarchar(4000)

set @LenVendor = 15

create table #CSV_Import (name VARCHAR(max), vendor VARCHAR(250))
create table #Import(name VARCHAR(max), vendor VARCHAR(250))
create table #vendor (vendor VARCHAR(250))
create table #t1 (name VARCHAR(max), vendor VARCHAR(250), num varchar(250))
create table #t2 (cat VARCHAR(max), vendor VARCHAR(250))
create table #t3 (cat VARCHAR(max))



--закачка csv
BULK INSERT #CSV_Import
FROM 'c:\imp\test1.csv'
WITH (codepage = '65001', firstrow=2, fieldterminator = ';', rowterminator = '\n')

--------------------------------приведение к нормальному виду (после BULK в вендор попадает часть названия)------------------------------
--обработка плохих строк
insert into #vendor 
select distinct vendor
from #CSV_Import where len(vendor) <= @LenVendor

DECLARE c_vendor CURSOR FOR SELECT vendor FROM #vendor order by len(vendor) desc
  OPEN c_vendor
	FETCH NEXT FROM c_vendor INTO @vendor
	  WHILE @@FETCH_STATUS = 0
		BEGIN
		  insert into #Import
		  select name + substring(vendor, 0, charindex(@vendor, vendor)), @vendor
		  from #CSV_Import
		  where len(vendor) > @LenVendor and charindex(@vendor, vendor) > 0

		  delete from #CSV_Import where len(vendor) > @LenVendor and charindex(@vendor, vendor) > 0

		  FETCH NEXT FROM c_vendor INTO @vendor
		END
  CLOSE c_vendor
DEALLOCATE c_vendor

--добавление нормальных записей
insert into #import
select name, vendor from #CSV_Import
where len(vendor) <= @LenVendor

--удаление " и ;
update #import set name = replace(replace(name, '"', ''), ';', '')

--select * from #import

set @vendor = ''
-------------------------------------------получение номенклатурного номера (обрабатываю первые 100)-------------------------------------------------

DECLARE c_num CURSOR FOR SELECT top 10 name, vendor FROM #import
  OPEN c_num
	FETCH NEXT FROM c_num INTO @name, @vendor
	  WHILE @@FETCH_STATUS = 0
		BEGIN
		  set @res = ''
		  set @i = 1
		  while @i < len(@name)
			begin
				select @res = substring(@name, len(@name) - @i, @i)
			    if charindex(' ', @res) > 0
				  break 
                else
				  begin
			        set @i = @i + 1
				    continue
				  end
			end --while
			insert into #t1
			select @name, @vendor, @res
          FETCH NEXT FROM c_num INTO @name, @vendor
	    END
CLOSE c_num
DEALLOCATE c_num

select * from #t1

--------------------------------------------------категория и бренд товара--------------------------------------
--в названии определен vendor
insert into #t2
select distinct rtrim(REPLACE(substring(name, 0, charindex(vendor, name)), char(160), ' ')), vendor
from #import
where charindex(vendor, name) > 0
union all

-----------ручные доработки
--в названии не определен vendor вместо Frogum Premium указан Frogum Proline, но поле vendor заполнено Frogum Premium
select distinct rtrim(REPLACE(substring(name, 0, charindex('Frogum Proline', name)), char(160), ' ')), vendor
from #import
where charindex(vendor, name) = 0 and vendor = 'Frogum Premium'
union all

-- в названиине не указан vendor для Frogum
select distinct 'Резиновый коврик универсальная перемычка', vendor
from #import
where charindex(vendor, name) = 0 and vendor = 'Frogum'


select *  from #t2 order by cat, vendor


insert into #t3 
select distinct cat from #t2

-----------------------------------------------------разворот таблицы-----------
set @alter_table = 'select cat.cat '
DECLARE c_vendor CURSOR FOR SELECT vendor FROM #vendor
  OPEN c_vendor
	FETCH NEXT FROM c_vendor INTO @vendor
	  WHILE @@FETCH_STATUS = 0
		BEGIN
		  set @alter_table = @alter_table +' , ' + '[' +cast(@vendor as nvarchar) + ']=' +' (select 1 from #t2 c where c.cat = cat.cat and c.vendor = ' + ''''+ @vendor +'''' +')'
  		  FETCH NEXT FROM c_vendor INTO @vendor
		END
CLOSE c_vendor
DEALLOCATE c_vendor

set @alter_table = @alter_table + 'from #t3 cat order by cat.cat
'
exec (@alter_table)

IF (OBJECT_ID('tempdb..#CSV_Import') IS NOT NULL) DROP TABLE #CSV_Import
IF (OBJECT_ID('tempdb..#Import') IS NOT NULL) DROP TABLE #Import
IF (OBJECT_ID('tempdb..#vendor') IS NOT NULL) DROP TABLE #vendor
IF (OBJECT_ID('tempdb..#t1') IS NOT NULL) DROP TABLE #t1
IF (OBJECT_ID('tempdb..#t2') IS NOT NULL) DROP TABLE #t2
IF (OBJECT_ID('tempdb..#t3') IS NOT NULL) DROP TABLE #t3