Trying to avoid using a cursor.... -- sql server programming

TomT
9/12/2004 9:43:01 AM

David,

Thanks for your reply. As I mentioned in my post, there's more to this than
the example I posted. I'm not trying to number rows, the example I gave was
simplified to make it easier to explain what I need to learn.

I'll try to explain more. We maintain a large list of products which are on
a government schedule. Suppliers provide us with spreadsheets of their
products which we process and add to the above list.

The spreadsheets are imported into a "holding area" table, where they are
processed before being transferred to the main table. We have to examine
these to see if they exist in the main table, has pricing has changed up or
down, etc.

For products that don't exist in the main table, one of the things that
needs to be done is to create a value for a column called CLIN. This is a way
the gov needs products categorized. E.g. a CLIN number appears this way:
1001-xxx, 1002-xxx, 1003-xxx, where the first 4 characters indicate a
category, and the xxx is just an incrementing number.

So, after importing the spreadsheet, I need to look at a value in another
column to get the category - the value will be between 1 and 6 (this is the
1001, 1002, etc. - these do not increment, just the numbers to the right of
the -, e.g. 1001-101, 1001-102, etc.). In the import table, the category (a
number 1-6) will be there, but the CLIN column will contain nulls.

Before building the CLIN, I have to check the main product table to get the
highest value for that category, so e.g. if I am working with category 3, I
have to find a max value which will be 1003-x where x is the number I need to
increment. E.g. say the highest number in that category is 1003-5578, the
next would be 1003-5579.

I would write 1003-5579 to the first row in the holding table, then I need
to increment it by 1 for each of the remaining rows (where the category is
3), 1003-5580, 1003-5581, and so on.

This needs to be done for each category. Each row can have a category
identifier of 1-6, a category of 6 would have a CLIN built like 1006-xxx.

I was trying to spare you all this detail, but I was afraid someone might
think, as you did, I was just trying to number rows, which is not what I need
to do. I know I can do this using a cursor, examining each row one at a time,
and then create the incremented value, but I was curious to see if there was
a way to do this without a cursor.

Thanks for your assistance with this.

[quoted text, click to view]

David Portas
9/12/2004 10:19:27 AM

[quoted text, click to view]

Why? If you want to add a row number then don't put it in the table,
calculate it when you query the data:

SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS row_num
,*
FROM Authors AS A

Tables have no inherent ordering so your request doesn't make sense unless
you define in what order the numbers are to be allocated.

--
David Portas
SQL Server MVP
--

nospam_mytrash9 NO[at]SPAM bellsouth.net
9/12/2004 5:57:55 PM

David, I don't think Tom is trying to setup a row # column, but rather a column
that is incremented by some number. He did state in his original post that the
situation he had presented was a much simplified version of a more complex one.

Tom, the increment value is only evaluated once when the update statement is
executed, thus the reason why all updated rows contain the same value. So you
can either increment via a cursor or a WHILE @@ROWCOUNT > 0 loop. The later
avoids a cursor and therefore should be faster most of the time. The exception
could be if there isn't an index on the column to be incremented AND there are a
large number of rows (perhaps > 1 million, will depend on hardware). Here's a
working example...

SET NOCOUNT ON

CREATE TABLE #Test (
TsID int IDENTITY,
TsIncCol int
)

INSERT INTO #Test VALUES(10)
INSERT INTO #Test VALUES(NULL)
INSERT INTO #Test VALUES(NULL)
INSERT INTO #Test VALUES(NULL)
INSERT INTO #Test VALUES(NULL)

SELECT *
FROM #Test

UPDATE #Test
SET TsIncCol = M.MaxValue + 1
FROM ( SELECT MAX(TsIncCol) AS MaxValue
FROM #Test
WHERE TsIncCol IS NOT NULL
) AS M,
( SELECT TOP 1
TsID
FROM #Test
WHERE TsIncCol IS NULL
) AS U
WHERE #Test.TsID = U.TsID

WHILE @@ROWCOUNT > 0
BEGIN
UPDATE #Test
SET TsIncCol = M.MaxValue + 1
FROM ( SELECT MAX(TsIncCol) AS MaxValue
FROM #Test
WHERE TsIncCol IS NOT NULL
) AS M,
( SELECT TOP 1
TsID
FROM #Test
WHERE TsIncCol IS NULL
) AS U
WHERE #Test.TsID = U.TsID
END

SELECT *
FROM #Test

DROP TABLE #Test

Mark

On Sun, 12 Sep 2004 10:19:27 +0100, "David Portas"
[quoted text, click to view]

TomT
9/12/2004 9:17:09 PM

Hugo,

Thanks very much for your input and solution, I will give this a try, and
take your advice regarding storing the number in two columns - much easier
than dealing with string conversions.

I appreciate your assistance in this....

Tom

[quoted text, click to view]

Hugo Kornelis
9/12/2004 9:46:53 PM

[quoted text, click to view]

Hi Tom,

Interesting requirement. For a fully set-based solution, you need to
define in what order the new products get their CLIN: Why will product A
be assigned 1003-5579 and product B 1003-5580 and not the other way
around? I understand from the description that this actually doesn't
matter to you, but it does matter to SQL Server when it's trying to
perform the action. So you'll have to find some column (or even a
combination of two or more columns) that is guaranteed to be unique among
the new products for a give category. For the solution below, I'll just
assume that you can find such a column and that it's named UniqueCol

I also advise you (if you haven't done this already) to store the CLIN in
two seperate columns: the category (stored either as 1, 2, 3 or as 1001,
1002, 1003) and the CLIN identifier (only the part after the dash). You
can still have the full CLIN in the table, but as a computed column. For
my solution, I'll suppose you follow this advise. If not, you'll have to
use some ugly substring and cast functions to make the query work.

Since you didn't provide DDL or INSERT statements that I could use to test
my solution, I'll give you an untested solution.

UPDATE StagingTable
SET CLIN_Id = (SELECT MAX (MainTable.CLIN_Id)
FROM MainTable
WHERE MainTable.Category = StagingTable.Category) +
(SELECT COUNT(*)
FROM StagingTable AS ST2
WHERE ST2.Category = StagingTable.Category
AND ST2.UniqueCol <= StagingTable.UniqueCol
AND ST2.CLIN_Id IS NULL)
WHERE CLIN_Id IS NULL

Best, Hugo
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Trying to avoid using a cursor....