Can I avoid this cursor

[ruioliveira]

Hi,

I am using a linked server in SQL, to a Progress DB and I have developed a Stored Procedure to import some data.
On the Progress table that I import into SQL I have some fields of type array that I have to split in several records, for that I use a cursor but it becomes very slow. Bellow there is a part of the code:

DECLARE CursorStock CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT Lj_Cod, mst_CodArt, Cor_Cod, Tam_Numero, Tam_Tam4, art_qtdtamexi,
Cast(Art_DataIni as DateTime) as DTEnt, Cast(Art_DtUltVnd as DateTime) as DtUlt
FROM OPENQUERY(MULTIPOS, 'SELECT AL.Lj_cod, AL.mst_CodArt, AL.Cor_Cod, TA.Tam_Numero,
TA.Tam_Tam4, AL.art_QtdTamExi, AL.Art_DataIni, AL.Art_DtUltVnd
FROM pub.mp_artl as AL inner join pub.mp_tam as TA on AL.empr_Cod = TA.Empr_Cod and AL.tam_Cod = TA.Tam_Cod
WHERE AL.empr_cod = 1 and art_qtdtotexi <> 0')

Set @Ano = Year(GetDate())
Set @Mes = Month(GetDate())

-- Delete existing records
DELETE StkLojas WHERE Ano = @Ano and Mes = @Mes

OPEN CursorStock
FETCH NEXT FROM CursorStock
INTO @CdLj, @CdArt, @CdCor, @N, @LstTam, @LstQtd, @DtEnt, @DtUlt

WHILE @@FETCH_STATUS = 0
BEGIN
-- Transform the array into records
Set @I = 1
Set @PosTam = 0
Set @PosQtd = 0
While @I <= @N
BEGIN
Set @PosTam = CharIndex(';', @LstTam)
Set @PosQtd = CharIndex(';', @LstQtd)
Set @CdTam = LTRIM(SubString(@LstTam, 1, (@PosTam -1)))
Set @Qtd = SubString(@LstQtd, 1, (@PosQtd -1))
Set @LstTam = SubString(@LstTam, (@PosTam +1), (Len(@LstTam) - @PosTam))
Set @LstQtd = SubString(@LstQtd, (@PosQtd +1), (Len(@LstQtd) - @PosQtd))
-- Grava cada tamanho na tabela
Insert Into StkLojas (Ano, Mes, CodLoja, CodArt, Cor, Tamanho, QtdStk, DtEntStk, DtUltVnd )
Values(@Ano, @Mes, @CdLj, @CdArt, @CdCor, @CdTam, @Qtd, @DtEnt, @DtUlt)

Set @I = @I + 1
END
-- next record ...
FETCH NEXT FROM CursorStock
INTO @CdLj, @CdArt, @CdCor, @N, @LstTam, @LstQtd, @DtEnt, @DtUlt
END
CLOSE CursorStock
DEALLOCATE CursorStock


Any help would bbe appreciated
Thanks in advance

Rui Oliveira

 

[NoCoolHandle]

What about

Insert Into StkLojas (Ano, Mes, CodLoja, CodArt, Cor,    Tamanho, QtdStk, DtEntStk, DtUltVnd )  

SELECT Lj_Cod, mst_CodArt, Cor_Cod, Tam_Numero, Tam_Tam4, art_qtdtamexi,
       Cast(Art_DataIni as DateTime) as DTEnt, Cast(Art_DtUltVnd as DateTime) as DtUlt 
 FROM OPENQUERY(MULTIPOS, 'SELECT AL.Lj_cod, AL.mst_CodArt, AL.Cor_Cod, TA.Tam_Numero, 
                           TA.Tam_Tam4, AL.art_QtdTamExi, AL.Art_DataIni, AL.Art_DtUltVnd 
          FROM pub.mp_artl as AL inner join pub.mp_tam as TA on AL.empr_Cod = TA.Empr_Cod and AL.tam_Cod = TA.Tam_Cod 
         WHERE AL.empr_cod = 1 and art_qtdtotexi <> 0')

That should do it as a set opporation.. It would if the datasource was SQL or Access or Excel or for that matter a textfile with the correct drivers


Rob