select every row with no WHERE clause with a WHILE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

select every row with no WHERE clause with a WHILE

Author

Topic

hearnie
Starting Member

49 Posts

Posted - 2005-04-11 : 10:01:46

can anyone point me in the right direction on how I go about using a while loop to select every row of a table, so that I can loop through the whole of a table row by row within a while loop.

Im trying to take everyones good advice here

and replace a cursor.

H.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-11 : 10:05:23

what is your cursor doing? can you do it set based?

while exists(select * from MyTable where (you condition = true))
... while stuff...
end

Go with the flow & have fun! Else fight the flow

hearnie
Starting Member

49 Posts

Posted - 2005-04-11 : 10:12:34

I dont have a WHERE clause in the select.
I basically want to loop through every row of a temporary table which contains data pulled from a flat file.
then for each row I need to check for existance of data in the table and either update or insert into it.
That is basically what the cursor I have is doing and I want to do that with a select statement but I dont have a WHERE clause to match it on.

Is there some way I can set the rowcount and use a while until that has reached the end of the table?

H.

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-11 : 10:26:11

You shouldn't need to use a cursor for this type of activity, or a LOOP.

Describe exactly the thing that you are trying to achieve and some nice person will find a set based method of doing it.

-------
Moo. :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 10:33:18

yeah, though while loops can have advantages over cursors, what most people here are advocating is doing away with looping all-together for the advantages of "set-based" sql.

typically, your problem is solved with something like:

--Update Existing
Update a Set
a.<cols> = b.<cols>
from RealTable a
JOIN #temp b ON a.<keys> = b.<keys>

--Insert New
Insert RealTable
SElect a.<cols>
from #temp a
left join RealTable b ON a.<keys> = b.<keys>
Where b.<key> is NULL

Be One with the Optimizer
TG

SamC
White Water Yakist

3467 Posts

Posted - 2005-04-11 : 10:36:43

You must be assimilated into the borg of set-based thinking. It is painless, and you will be happier once this is done. Now hold still...

quote:
I basically want to loop through every row of a temporary table which contains data pulled from a flat file.
then for each row I need to check for existance of data in the table and either update or insert into it.
That is basically what the cursor I have is doing and I want to do that with a select statement but I dont have a WHERE clause to match it on.

UPDATE MyTable
  SET MyColumn = expression
  WHERE MyColumn = SomeDataValue

hearnie
Starting Member

49 Posts

Posted - 2005-04-11 : 10:40:57

Ok, I hope some nice person can....that would be great

Ok, I have 2 temporary tables with data filled from flat files
I want to loop through every row in the Table1 and query one of my tables in the database RealData1 for the presence of the info from that particular row.
If the data isnt there, I want to insert....otherwise I want to insert.
However, when I update I also have to take data from the Temp2Table to insert into my RealData2 table which is linked by a foreign key to RealData1

So Pseudo what Im doing with Cursor is as follows

CREATE PROCEDURE myloopingproc AS
DECLARE
.........all my vars here

DECLARE myDatacursor CURSOR FOR
SELECT field1,field2,field3,field4 from TempDataTable1

OPEN myDatacursor

FETCH FROM myDatacursor INTO @var1,@var2,@var3,@var4

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF EXISTS (SELECT * FROM TempDataTable2 WHERE field1=@var1)
BEGIN
SELECT @match = (SELECT FROM TEMPTABLE2 WHERE sum=@var3 and total=@var4)

END

IF NOT EXISTS (SELECT * FROM REALDATATABLE WHERE....)
BEGIN
--do the insertions
INSERT INTO REALDATATABLE (...fields)
VALUES (....values)

select @my_id = @@identity

END
ELSE
BEGIN
IF EXISTS (SELECT * FROM REALDATATable WHERE col1= @var1)
BEGIN
UPDATE REALDATATable
SET name = @var3
WHERE .....
END

END

--this section handles the insertion or updates to the second table
IF NOT EXISTS (SELECT * FROM REALDATATable2 WHERE field_id=@my_id)
BEGIN
INSERT INTO REALDATATable2 (....fields)
VALUES (...@values)

END
ELSE
BEGIN
UPDATE REALDATATable2

END

FETCH NEXT FROM curInkingData INTO @var1,@var2,@var3,@var4
END

CLOSE MyDatacursor
DEALLOCATE MyDatacursor
GO

hope that makes sense

hearnie
Starting Member

49 Posts

Posted - 2005-04-11 : 10:46:23

I kind of see where your going with the below, but its the fact that I have 2 tables, one with a foreign key relationship to the first that need to be updated......so as I update one row in one table, I then need to nestedly (maybe???) update the second table at the same time, from data taken from a second temp table.

I can see how the solution below works nicely on 1 table - to - 1 temp table row manipulation......but dont have a clue when you bring in the other 2 tables.

Thanks for the help guys.

H.

quote:
Originally posted by TG

yeah, though while loops can have advantages over cursors, what most people here are advocating is doing away with looping all-together for the advantages of "set-based" sql.

typically, your problem is solved with something like:

--Update Existing
Update a Set
a.<cols> = b.<cols>
from RealTable a
JOIN #temp b ON a.<keys> = b.<keys>

--Insert New
Insert RealTable
SElect a.<cols>
from #temp a
left join RealTable b ON a.<keys> = b.<keys>
Where b.<key> is NULL

Be One with the Optimizer
TG

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-11 : 11:01:55

Hmm. Well based on what you were asking for you would need a two step process similar to this

-- INSERT

INSERT INTO yourbigtable SELECT * FROM yourtemptable1 y1 LEFT OUTER JOIN yourbigtable yb ON y1.keyfield = yb.keyfield WHERE yb.keyfield IS NULL

-- UPDATE

BEGIN TRAN
UPDATE yourbigtable SET yourbigtable.value = yourtemptable1.value, etc FROM yourbigtable INNER JOIN yourtemtable1 ON keyfield=keyfield

INSERT INTO yourbigtable2 SELECT * FROM yourtemptable2 y2 LEFT OUTER JOIN yourbigtable yb ON y2.keyfield = yb.keyfield WHERE yb.keyfield IS NULL
UPDATE yourbigtable2 SET yourbigtable.value = yourtemptable2.value, etc FROM yourbigtable2 INNER JOIN yourtemtable2 ON keyfield=keyfield

COMMIT

Maybe.

-------
Moo. :)

hearnie
Starting Member

49 Posts

Posted - 2005-04-11 : 11:07:16

by the way......when running the cursor in a stored procedure I was getting an error intermittently that said ......'could not complete cursor operation because table schema changed after cursor was declared'.......now! Does anyone, anywhere have the foggiest idea what that means

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 13:55:52

quote:
by the way......when running the cursor in a stored procedure I was getting an error intermittently that said ......'could not complete cursor operation because table schema changed after cursor was declared'.......now! Does anyone, anywhere have the foggiest idea what that means

My guess: Since your "temp" table is not really a #temp table but actually a permenent table that is named temp..., sounds like someone else is running the process that creates that table while you are running the process that is cursoring through the records.

Be One with the Optimizer
TG

hearnie
Starting Member

49 Posts

Posted - 2005-04-12 : 05:28:56

can you explain to me the difference between me setting up or creating a table named 'TempTable1' in a DTS package, and the #temp. What is the #temp?

cheers

H.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-12 : 05:30:32

#temp is a temporaray table stored in tempDb. it persists over a connection, then it's destroyed.
your kind is just a regular table that's stored in your db.

Go with the flow & have fun! Else fight the flow

hearnie
Starting Member

49 Posts

Posted - 2005-04-12 : 05:46:24

and would my kind of table cause errors within the stored procedure if cursors were operating on it, as opposed to if I used the #temp table?

Also, can this #temp table be created outside of the stored procedure in a SQL TASK of the DTS package and once the package finishes will it be dropped?

cheers

H.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-12 : 06:00:38

yes if someone altered your table while cursor is opened on it
and yes. try it.

Go with the flow & have fun! Else fight the flow

hearnie
Starting Member

49 Posts

Posted - 2005-04-12 : 07:10:00

ok in the case sample provided above

--Update Existing
Update a Set
a.<cols> = b.<cols>
from RealTable a
JOIN #temp b ON a.<keys> = b.<keys>

--Insert New
Insert RealTable
SElect a.<cols>
from #temp a
left join RealTable b ON a.<keys> = b.<keys>
Where b.<key> is NULL

How would I modify this, so that when I inserted into the table...for each insert their maybe multiple rows assosiated with this insert in a second table. So for each insert I need to get the IDENTITY of the row just inserted and insert multiple rows with this identity into the second table before the next Insert happens on the first table.
Likewise if I update, table one. I need to update table2 also with corresponding values.

?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-12 : 08:07:08

Do you think this will work for your situation?


Ok you've got temp1, temp2, real1, real2
one to many real1 to real2 with FK constraint (real1 PK -> real2 FK)

This solution assumes you have columns(s) in temp2 that associate them to the appropriate
record in temp1.

--update real1
Update a Set
	a.<cols> = b.<cols>
from	Real1 a
JOIN	temp1 b ON a.<keys> = b.<keys>

--insert real1 (which will generate new PKs)
Insert Real1 (<colst>)
SElect	a.<cols>
from	temp1 a
left join Real1 b ON a.<keys> = b.<keys>
Where	b.<key> is NULL

--update real2 (existing values)
Update a Set
	a.<cols> = b.<cols>
from	Real2 a
JOIN	temp2 b ON a.<keys> = b.<keys>

--insert real2 (including FKs - real1 PKs)
--(you may be able to eliminate the join to temp1 if you have cols in temp2 that associate to real1)
Insert Real2 (FKs, <cols>)
SElect 	c.PKs, a.<cols>
from 	temp2 a
JOIN 	temp1 b ON a.<associationCols> = b.<associationCols>
JOIN	real1 c ON b.<cols> = c.<cols>
left join Real2 d ON d.<PKs> = c.<PKs>
Where	d.<PK> is NULL

Be One with the Optimizer
TG

hearnie
Starting Member

49 Posts

Posted - 2005-04-12 : 08:18:04

thanks a million for the help there.
I think it might make it a bit easier if I left outer join the temp1&2 into one temp table and work off that? Do you think?

Anyhow.....the problem I had with the cursor not updating because the table schema had changed......this is because, or at least its what appears to be, the database is deleting my temporary tables before the stored procedure completes? WHY on earth would it be doing this? I dont understand why tables I create within a DTS package or deleted mid stored procedure that is also in the same DTS package? any takers?

H.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-12 : 08:28:00

"this is because, or at least its what appears to be, the database is deleting my temporary tables before the stored procedure completes? WHY on earth would it be doing this?"

If you are using a #temp, that is a LOCAL temporary table. Once it goes out of scope, it is destroyed. Try using a global temp table (##temp).
There are arguments for and against using them. I would use a table variable if at all possible.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources