Bonediggler1
Technical User
Hi -
To simplify: I have a table with about 10 fields but the update in question only involves 2, Invoice (INV) and Sequence Number (SEQ_NUM).
Basically I pull the data from Oracle and put it in SQL Server. In oracle there will be several transactions per invoice, which are grouped into sequences (00, 01, 02 etc). However, SEQ_NUM is only populated for the first invoice in a sequence, so the data would look like:
INV SEQ_NUM
12 00
12
12 01
12
12
12 02
I pull this into SQL Server and then use the following code to update the missing SEQ_NUMs:
DECLARE @V VARCHAR(20)
DECLARE @X VARCHAR(20)
DECLARE @Y INT
DECLARE @Z INT
DECLARE @COUNTER INT
DECLARE @COUNTER1 INT
SET @COUNTER = 1
SET @COUNTER1 = (SELECT COUNT(*) FROM TABLE)
SET @Y = NULL
SET @V = NULL
WHILE @COUNTER <= @COUNTER1
BEGIN
SET @Z = (SELECT SEQ_NUM FROM TABLE WHERE ROWID = @COUNTER)
SET @X = (SELECT INV FROM TABLE WHERE ROWID = @COUNTER)
IF @X = @V
IF @Z IS NULL
UPDATE TABLE SET SEQ_NUM = @Y WHERE ROWID = @COUNTER
SET @Y = (SELECT SEQ_NUM FROM TABLE WHERE ROWID = @COUNTER)
SET @V = (SELECT INV FROM TABLE WHERE ROWID = @COUNTER)
SET @COUNTER = @COUNTER + 1
END
The table is sorted so to look like the sample data above, and ROWID assigns a row number to each record, starting at 1.
This works, but takes a really long time - i am updating more than 3 million records.
Any ideas on how to improve performance?
Thank you!!
To simplify: I have a table with about 10 fields but the update in question only involves 2, Invoice (INV) and Sequence Number (SEQ_NUM).
Basically I pull the data from Oracle and put it in SQL Server. In oracle there will be several transactions per invoice, which are grouped into sequences (00, 01, 02 etc). However, SEQ_NUM is only populated for the first invoice in a sequence, so the data would look like:
INV SEQ_NUM
12 00
12
12 01
12
12
12 02
I pull this into SQL Server and then use the following code to update the missing SEQ_NUMs:
DECLARE @V VARCHAR(20)
DECLARE @X VARCHAR(20)
DECLARE @Y INT
DECLARE @Z INT
DECLARE @COUNTER INT
DECLARE @COUNTER1 INT
SET @COUNTER = 1
SET @COUNTER1 = (SELECT COUNT(*) FROM TABLE)
SET @Y = NULL
SET @V = NULL
WHILE @COUNTER <= @COUNTER1
BEGIN
SET @Z = (SELECT SEQ_NUM FROM TABLE WHERE ROWID = @COUNTER)
SET @X = (SELECT INV FROM TABLE WHERE ROWID = @COUNTER)
IF @X = @V
IF @Z IS NULL
UPDATE TABLE SET SEQ_NUM = @Y WHERE ROWID = @COUNTER
SET @Y = (SELECT SEQ_NUM FROM TABLE WHERE ROWID = @COUNTER)
SET @V = (SELECT INV FROM TABLE WHERE ROWID = @COUNTER)
SET @COUNTER = @COUNTER + 1
END
The table is sorted so to look like the sample data above, and ROWID assigns a row number to each record, starting at 1.
This works, but takes a really long time - i am updating more than 3 million records.
Any ideas on how to improve performance?
Thank you!!