support
Support
Количество сообщений
1199
Зарегистрирован:
11-04-2008, 19:11:57
|
Скрипт удаления дубликатов товаров из прайса
Такой дополнительный SQL-запрос для схемы импорта может выглядеть вот так:
Код
DECLARE @l_sNAME varchar(8000), @l_sSID varchar(8000), @l_iID int;
DECLARE cDublicates CURSOR LOCAL FORWARD_ONLY LOCAL FOR
select distinct td.G_NAME, td.G_SID
from
(
select t2.cnt, t2.G_NAME, t2.G_SID
from
(
select count(*) as cnt, t.G_NAME, t.G_SID
from
(
select IsNull(UPPER(LTRIM(RTRIM(CAST(prc.NAME as varchar(8000))))), '') as G_NAME, IsNull(UPPER(LTRIM(RTRIM(CAST(prc.SID as varchar(8000))))), '') as G_SID
from #TMP_PRICE prc
) t
where t.G_SID = ''
group by t.G_NAME, t.G_SID
) t2
where t2.cnt > 1
) td FOR READ ONLY;
OPEN cDublicates;
FETCH NEXT FROM cDublicates INTO @l_sNAME, @l_sSID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
select @l_iID =
(
select TOP 1 ID from
(
select ID, name, SID, case when IsNumeric(price.PRICE) = 1 then CAST(REPLACE(price.PRICE, ',', '.') as money) else 0 end as PRICE
from
(
select ID,
IsNull(UPPER(LTRIM(RTRIM(CAST(NAME as varchar(8000))))), '') as name,
IsNull(UPPER(LTRIM(RTRIM(CAST(SID as varchar(8000))))), '') as SID,
IsNull(UPPER(LTRIM(RTRIM(CAST(PRICE as varchar(8000))))), '0') as PRICE
from #TMP_PRICE
) price
where price.NAME = @l_sNAME
and price.SID = @l_sSID
) t
order by PRICE DESC
);
DELETE FROM #TMP_PRICE
WHERE (ID <> @l_iID)
and IsNull(UPPER(LTRIM(RTRIM(CAST(NAME as varchar(8000))))), '') = @l_sNAME
and IsNull(UPPER(LTRIM(RTRIM(CAST(SID as varchar(8000))))), '') = '';
FETCH NEXT FROM cDublicates INTO @l_sNAME, @l_sSID;
END
CLOSE cDublicates;
DEALLOCATE cDublicates;
DECLARE cDublicates CURSOR LOCAL FORWARD_ONLY LOCAL FOR
select distinct td.G_NAME, td.G_SID
from
(
select t2.cnt, IsNull(UPPER(LTRIM(RTRIM(CAST(t3.NAME as varchar(8000))))), '') as G_NAME, t2.G_SID
from
(
select count(*) as cnt, t.G_SID
from
(
select IsNull(UPPER(LTRIM(RTRIM(CAST(prc.SID as varchar(8000))))), '') as G_SID
from #TMP_PRICE prc
) t
group by t.G_SID
) t2, #TMP_PRICE t3
where t2.cnt > 1
and t2.G_SID <> ''
and t2.G_SID = IsNull(UPPER(LTRIM(RTRIM(CAST(t3.SID as varchar(8000))))), '')
) td FOR READ ONLY;
OPEN cDublicates;
FETCH NEXT FROM cDublicates INTO @l_sNAME, @l_sSID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
select @l_iID =
(
select TOP 1 ID from
(
select ID, name, SID, case when IsNumeric(price.PRICE) = 1 then CAST(REPLACE(price.PRICE, ',', '.') as money) else 0 end as PRICE
from
(
select ID,
IsNull(UPPER(LTRIM(RTRIM(CAST(NAME as varchar(8000))))), '') as name,
IsNull(UPPER(LTRIM(RTRIM(CAST(SID as varchar(8000))))), '') as SID,
IsNull(UPPER(LTRIM(RTRIM(CAST(PRICE as varchar(8000))))), '0') as PRICE
from #TMP_PRICE
) price
where price.NAME = @l_sNAME
and price.SID = @l_sSID
) t
order by PRICE DESC
);
DELETE FROM #TMP_PRICE
WHERE (ID <> @l_iID)
-- and IsNull(UPPER(LTRIM(RTRIM(CAST(NAME as varchar(8000))))), '') = @l_sNAME
and IsNull(UPPER(LTRIM(RTRIM(CAST(SID as varchar(8000))))), '') = @l_sSID;
FETCH NEXT FROM cDublicates INTO @l_sNAME, @l_sSID;
END
CLOSE cDublicates;
DEALLOCATE cDublicates;
Его нужно вставить в схему импорта в поле "SQL-скрипт после чтения данных"
|
