luissantos (10/10/2012)
Hello MickyThe purpose for using a Cursor is because i need to run an Update not for one "referencia" = Article, but for more and less 170000, and for each one the number of "tam" could be different like 34,35,36,37,38,39 or for another one ; 32,33,34,35 and so on....
Note : for testing my cursor the WHERE condition have only one referencia.
I think it is possible to alter my cursor to make an IF condition, ok i not doing this right, but if you can explain how to do this i will be grateful.
I hope you can understand what i pretend.
Many thanks for your reply,
Luis Santos
Looks to me like Eugene and Micky are correct - there's no need to cripple this process by using a cursor.
Have a look through the following code, I think the logic should be very clear.
If it doesn't meet your requirement then I think the spec needs a few more details.
SELECT
referencia= RTRIM(referencia),
TAMANHO= RTRIM(TAMANHO),
sgtstamp= RTRIM(LEFT(NEWID(),23)),
SORTIMENTO= min(QTD),
Rownumb= ROW_NUMBER() OVER(PARTITION BY referencia ORDER BY referencia,tamanho ASC)
INTO #arttamcor
FROM arttamcor
WHERE referencia = '30110' --IN('29913','30110')
GROUP BY referencia, TAMANHO
-- insert tam = 'Y' rows into SGT where they don't already exist
INSERT INTO SGT (
sgtstamp,
ref,
Tam,
pos,
dimcol,
ousrdata,
ousrhora,
ousrinis,
usrdata,
usrhora,
usrinis)
SELECT
sgtstamp,
referencia,
'Y',
rownumb,
SORTIMENTO,
CAST(GETDATE() AS DATE),
CONVERT (time, GETDATE()),
'ADM',
CAST(GETDATE() AS DATE),
CONVERT (time, GETDATE()),
'ADM'
FROM #arttamcor a
WHERE NOT EXISTS (SELECT 1 FROM SGT s WHERE s.ref = a.ref AND s.tam = 'Y')
-- insert all of the rows
INSERT INTO SGT(
sgtstamp,
ref,
Tam,
pos,
dimcol,
ousrdata,
ousrhora,
ousrinis,
usrdata,
usrhora,
usrinis)
SELECT
sgtstamp,
referencia,
Tamanho,
rownumb,
SORTIMENTO,
CAST(GETDATE() AS DATE),
CONVERT (time, GETDATE()),
'ADM',
CAST(GETDATE() AS DATE),
CONVERT (time, GETDATE()),
'ADM'
FROM #arttamcor a
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]