Hello,

Suppose the following:

DECLARE test CURSOR FOR SELECT nameID, firstName FROM testing
DECLARE @array TABLE ( nameID INT NOT NULL,
firstName VARCHAR(32) NOT NULL )

DECLARE arrayCursor CURSOR SCROLL FOR SELECT nameID, firstName from
@array
DECLARE @index INT, @count INT, @offset INT
DECLARE @newID INT, @newName VARCHAR(32)
DECLARE @oldID INT, @oldName VARCHAR(32)

SET @count = (SELECT COUNT(*) FROM testing)
SET @offset = 11

The idea is to use the temporary table as an array to hold the data that
I want to move. I would also like to delete data from the "array" when I
have finished using it so I may insert more and keep the table small.

SET @index = 0
OPEN arrayCursor
FETCH FIRST FROM test INTO @oldID, @oldName
WHILE (@@FETCH_STATUS = 0 AND @index < @count - @offset) BEGIN
INSERT INTO @array VALUES (@oldID + @offset, @oldName)
FETCH NEXT FROM arrayCursor INTO @newID, @newName
DELETE FROM @array WHERE nameID = @newID
UPDATE testing SET firstName = @newName WHERE nameID = @oldID

FETCH NEXT FROM test INTO @oldID, @oldName
SET @i = @i + 1
END

After I grab a row from the "array" I would like to delete it and add the
next row to the array. In that way, I should only have (@offset + 1) rows
in the array at any time. It seems that after the delete, the arrayCursor
is not pointing where I think it should, i.e., the next row on the top (I
guess what I really want is a queue)

Thanks,

[quoted text, click to view]
that I want to move. I would also like to delete data from the "array"
when I have finished using it so I may insert more and keep the table
small. <<

You have missed the **entire point** of an RDBMS and returned to 1950's
sequential file processing. SQL is a set-oriented language. You
define a set of data and operate on it as a set. You do not buffer it
into arrays. You do not process it one "record" at a time.
Can you post DDL and tell us what you actually want to do?

Without more information I'm not certain what you are trying to do here but
I'm pretty sure you don't need a cursor to do it. You should try to avoid
cursors where possible. This article explains the best way to describe your
problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006

As Celko has said, it isn't normally sensible to try to imitate an "array"
in SQL. Possibly you are just trying to represent an ordered list in a table
but without seeing some sample data this is only a guess. Say your table and
data look like this:

CREATE TABLE foo (firstname VARCHAR(32) PRIMARY KEY, ord INTEGER NOT NULL
UNIQUE)

INSERT INTO foo (firstname, ord)
SELECT 'A', 1 UNION ALL
SELECT 'B', 2 UNION ALL
SELECT 'C', 3 UNION ALL
SELECT 'D', 4

You can re-order an item in the list as follows. I'm assuming there are no
gaps in the sequence - again, some sample data would have made your
requirements clearer.

DECLARE @old_idx INTEGER, @new_idx INTEGER

SET @old_idx = 4
SET @new_idx = 2

UPDATE foo SET ord =
CASE
WHEN ord = @old_idx THEN @new_idx
WHEN ord BETWEEN @old_idx AND @new_idx THEN ord - 1
WHEN ord BETWEEN @new_idx AND @old_idx THEN ord + 1
END
WHERE ord BETWEEN @old_idx AND @new_idx
OR ord BETWEEN @new_idx AND @old_idx

Hope this helps.

--
David Portas
SQL Server MVP
--

"--CELKO--" <jcelko212@earthlink.net> wrote in
news:1104556510.976101.225030@c13g2000cwb.googlegroups.com:

[quoted text, click to view]

Say I have a simple database with two columns repreesenting an ID and a
first name. The data is real (in my actual databse I have more colums
with last name, social, phone number, etc). I would like to mangle the
data by associating data with different ID numbers according to an
offset. If my offset is 11, for example, I would take the first name in
the database and assign it to ID number 12 (or the 12th ID; the IDs need
not be sequential). In this manner, and with different offsets for
different columns, I can assure that while the data is real, it looks
nothing like the original.

Thanks for your patience. I come from a procedural language background,
so this is the only way I think.

Try this:

UPDATE YourTable
SET id =
(SELECT COUNT(*)+11
FROM YourTable AS T
WHERE firstname <= YourTable.firstname)

However, if your "ID" is really a ranking based on the Firstnames then you
are probably better off not storing it in the table at all - just generate
it when you query the table.

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Andrew,

But it'll also quite likely violate many of your constraints. Just what
will your table look like if, after the mangling, the row with my name
gets your date of birth and your father's date of marriage - it would show
me being married before even being born!!

I'd like to know why you need this mangled data. If you need it for
testing, think again. Testing with "real" data often doesn't cover all
possible cases, plus the amount of output is typically so large that
nobody will ever be able to completely check all output. Structured
testing means that you use a description of the program's logic to create
a set of test cases that is varied enough to cover all possible
situations, yet small enough to enable manual checking (or rather:
prediction) of the results.

Best, Hugo
--