Loop Query - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Loop Query

Author

Topic

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-04 : 14:36:10

I apologize now, if this is an elementary question. I am trying to pull data from a warehouse, but there are too many rows and the query times out. My thought was to create a stored procedure that was a loop query and pull that into the job. The two queries I was using in the job to obtain data from a small data set (and they work fine) is...

insert into tableX
select r.field1, u.field2, u.field3, u.field4, u.field5
from localdb.dbo.tableR r left join openquery (warehouse,'
select field1, field2, field3, field4, field5
from schema.tableU') u on r.field1=u.field1
where r.done=1

update tableR
set done=0
from localdb.dbo.tableX, localdb.dbo.tableR
where tableX.field1=tableR.field1 and
tableX.field2 is null and tableX.field3 is null and tableX.field4 is null and tableX.field5 is null

Unfortunately, I have not been able to find information on how to create a Loop query (yes I have checked Books Online). I am sure I am looking in the wrong place and now I am frustrated. Thank you for any assistance you can spare my way.

*************************
Got some code from Rob. Can anyone help?

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-04 : 15:05:25

try

creating your statement in a way that it works more efficiently or
building index or
set the timeout = 0 (unlimited)
search for While in BOL or
at last resource search in BOL for cursors

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-04 : 15:10:04

maybe you should put an identity in tableR and do

while (select max(id) from tableR) <= select count(*) from openquery...)
begin
select ...
from tableR ...
where id between n and m.

update....
end

or something like that...

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

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-04 : 15:21:22

An identity? I have a unique id (field1) and a verify id (done starts as 1, then updated to 0 when info is found for field1). Is that what you mean? If that's the case, then how do I set the "where id between n and m"? I won't know how many rows there are. I apologize for seeming dense and I appreciate your assistance.

*************************
Got some code from Rob. Can anyone help?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-04 : 15:22:01

is the source database SQL Server also? If so -- don't use openquery(), use linked servers.

Another idea, if you must use OpenQuery (i.e, importing from ODBC) is to first put the data into a temp table, and then do your left outer join to the temp table in SQL Server. That should perform much better in most cases.

Finally -- in your OpenQuery(), put in as much criteria as possible. Don't count on the join to filter the rows -- do it as much as you can within the openquery, even if it is redundant. For example, suppose that the join condition effectly limits the data in the OpenQuery() part so that all rows have a status of 'A'. Instead of using the JOIN to do this filter, add it into the OpenQuery as a where clause. Put as much in there as you can.

Hope this gives some ideas....

- Jeff

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-04 : 15:26:42

The unique id is located in SQL Server, however the data I need to pull is a linked server. I was told that Openquery is faster and more efficient. I don't know how to do "linked server" in a query. I also don't know how to pull imported data into a temp table. I have tried to do that in the past, because I had assumed it would be better, but couldn't get it to work. Can't remember if I ran out of space or what. The Openquery has as much criteria as it needs. The reason for the join is to limit the rows to those unique ids. I don't know how to put that type of criteria in an Openquery. Please continue to offer assistance or guide me to a resource that could help.

*************************
Got some code from Rob. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-04 : 16:03:45

Hey...it's been a long time...where you been?

Chasing Jr. around the country?

Let me ask this.

What do you expect the full result set to be?

And do you have access/authority on the other box?

And is it sql server?

Brett

8-)

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-04 : 16:26:24

Wow, didn't realize I was missed. I'll try not to stay away as much :D. Yes, I have been chasing Jr. around the country, but he is turning his attention to much more sexier women than I. *sigh* Oh well. Guess I better stick to my SQL!

SQL Server is what I am using to pull in the data. I do have access to but do not have authority on the other box. The Openquery is connecting to Oracle. To be clear, so no one yells at me again (smile), I am not asking for P-SQL advice! :D

I am hoping to pull relational data to my unique id's located on the SQL Server. I have to look to three different tables. Two of the tables contain one month of data, where as this last table contains nine months of data. It is not my SQL that times out but the Oracle db that shuts me off, if I am on too long. Probably because they shut down the server to do maintenance at night.

I am looking to return over 10,000 rows of data by 10 fields wide. The last time I ran this, it failed after 12hrs.

*************************
Got some code from Rob. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-04 : 16:59:54

How many rows in tablex?

Looking at this, I would think ALL the rows from the Oracle table are being pulled across, then the join is being evaluated.

Not Good.

What is the timelyness of the data?

I would suggest you get am unload of the Oracle table and bcp it in to your server...apply appropriate indexes the run your query.

If not that, I might try and build a sql statement that contains a list of all the id's I wanted and pass that in the open query to oracle...but that might not be feasible...

PS...I have a question

How did you get your handle? And from Whom?

And where in USA are you? Down Daytona way?

Brett

8-)

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-04 : 17:16:56

Please don't make a fuss about my "handle" as that creates undo tension in the site. I really like it and I do not want it taken away from me again. Just like everyone else, I did not choose what my "handle" says and I am not sure who on SQL Team gave it back to me. My heart is in Daytona, but I am from Maryland. :D

There are millions of rows in tableX. I don't know what you mean by "get am unload of the Oracle table and bcp it in to your server". I can pull in the data if I am only asking for like 1000 matches, but I dont want to have to create 15 steps in the job to get that done. Besides...if there are more than 15000 rows I need then that wouldnt work either. Doing a Loop Query should help, but I dont know how to write a Loop Query.

Thank you so much for your assistance. I am eagerly awaiting further advice.

*************************
Got some code from Rob. Can anyone help?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-04 : 20:33:48

Since you've already got your table with ID's in it, this should work:

DECLARE @sql varchar(8000), @ids varchar(8000)

WHILE EXISTS(SELECT * FROM myTable WHERE Done=0)
BEGIN
 SELECT @sql='', @ids=''
 SELECT TOP 500 @ids=@ids + cast(ID as varchar) + ',' FROM myTable WHERE Done=0
 SET @sql='UPDATE myTable SET Done=1 WHERE ID IN(' + @ids + 'null)'
 EXEC(@sql)
 SET @sql='SELECT * FROM OPENQUERY(linkServer, ''SELECT * FROM OracleTable WHERE ID IN(' + @ids + ')'''
 INSERT INTO otherTable EXEC(@sql)
END

Obviously you'll have to change the table, column, and linked server names to the appropriate values, but this should loop through the entire IDs table until no more are left to be done.

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-04 : 20:42:41

As always Rob - you are awesome! Thank you soooooo much. If I get any errors, I'll check back with you to see if it's my syntax. Again - thanks!

*************************
Got some code from Rob. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-05 : 10:33:32

quote:
Originally posted by X002548

If not that, I might try and build a sql statement that contains a list of all the id's I wanted.

Pass that in the open query to oracle...

but that might not be feasible...

quote:

Like I told you, what I said,
Steal your face right off your head.

Ledell, tried to reply but your email bounced...

Do you understand what Rob's saying?

You need to pass the request to the server so it can be resolved on the Oracle box.

Still, since it's a join, just doing 500 at a time might cause duplicates if id is repeated many times.

SELECT DISCTINCT ID perhaps

And I wanted to know how many rows are in the Oracle table. You said Tablex (sql server?) had millions.

Here's another way at this.

Tell us what it's suppose to be doing in business terms.....ya know, like, why are you doing this in the first place.

Brett

8-)

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-05 : 10:52:03

Brett - Thanks for trying to help me with this. It really is appreciated.

The email address in my profile is no longer valid. I changed it yesterday, but it doesn't want to stay changed. It keeps going back to the old email (which is no longer valid). I put the new address in "Latest News".

I am trying to pull related data in Oracle to the unique IDs in SQL Server. The table on SQLServer (tableR) has only unique IDs, aprox 10,500 for this last query. The Oracle table (tableX) has millions of rows.

I am pretty sure I understand Rob's answer, however, I do have troubles sometimes modifying the code to what I need. I am getting the following error message...

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'false_churn'.

DECLARE @sql varchar(8000), @ids varchar(8000)

WHILE EXISTS(SELECT * FROM false_churn.dbo.rel_nar_id WHERE Done=1)
BEGIN
SELECT @sql='', @ids=''
SELECT TOP 500 @ids=@ids + cast(ID as varchar) + ',' false_churn.dbo.rel_nar_id WHERE Done=1
SET @sql='UPDATE false_churn.dbo.rel_nar_id SET Done=0 WHERE ID IN(' + @ids + 'null)'
EXEC(@sql)
SET @sql='SELECT * FROM OPENQUERY(linkServer, ''SELECT * FROM ndw.bsonarview.nci_nar_units_hist_2004_btv WHERE ID IN(' + @ids + 'null)'''
INSERT INTO false_churn.dbo.fc_info2 EXEC(@sql)
END

Any idea where I went wrong?

*************************
Got some code from Rob. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-05 : 10:58:48

Could it be that you're missing a FROM?


DECLARE @sql varchar(8000), @ids varchar(8000)

WHILE EXISTS(SELECT * FROM false_churn.dbo.rel_nar_id WHERE Done=1)
  BEGIN
	SELECT @sql='', @ids=''
	SELECT TOP 500 @ids=@ids + cast(ID as varchar) + ',' FROM false_churn.dbo.rel_nar_id WHERE Done=1
	SET @sql='UPDATE false_churn.dbo.rel_nar_id SET Done=0 WHERE ID IN(' + @ids + 'null)'
	EXEC(@sql)
	SET @sql='SELECT * FROM OPENQUERY(linkServer, ''SELECT * FROM ndw.bsonarview.nci_nar_units_hist_2004_btv WHERE ID IN(' + @ids + 'null)'''
	INSERT INTO false_churn.dbo.fc_info2 EXEC(@sql)
  END

Brett

8-)

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-05 : 11:06:13

quote:
Originally posted by X002548

Could it be that you're missing a FROM?

That could be it!

Silly me.

*************************
Got some code from Rob. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-05 : 11:10:35

Just for fun....

Can you tell us what this returns?



SELECT COUNT(DISTINCT [ID]) 
  FROM false_churn.dbo.rel_nar_id 
 WHERE Done=1

Brett

8-)

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-05 : 11:12:55

Your query request returns...
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ID'.

But this modification returns values...
SELECT COUNT(DISTINCT [rel_nar_id])
FROM false_churn.dbo.rel_nar_id
WHERE Done=1

I decided to delete that one post, after I took out the "--" before the "WHILE..." - because the query ran fine after that modification.

Again - Thank you everyone (but esp Rob and Brett) for your assistance with this. I am re-running all of the queries associated with this particular job. If I run into trouble with it, I will reach out again.
*************************
Got some code from Rob. Can anyone help?

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-06 : 14:17:40

modified the code to ...

DECLARE @sql varchar(8000), @rel_nar_id varchar(8000)

WHILE EXISTS(SELECT * FROM false_churn.dbo.rel_nar_id WHERE Done=1)
BEGIN
SELECT @sql='', @rel_nar_id=''
SELECT TOP 500 @rel_nar_id=@rel_nar_id + cast(rel_nar_id as varchar) + ',' FROM false_churn.dbo.rel_nar_id WHERE Done=1
SET @sql='UPDATE false_churn.dbo.rel_nar_id SET Done=0 WHERE rel_nar_id IN(' + @rel_nar_id + 'null)'
EXEC(@sql)
SET @sql='SELECT * FROM OPENQUERY(ndw, ''SELECT * FROM bsonarview.nci_nar_units_prev_btv WHERE rel_nar_id IN(' + @rel_nar_id + 'null)'''
INSERT INTO false_churn.dbo.fc_info2 EXEC(@sql)
END

got error message...
Executed as user: server\userid. Line 1: Incorrect syntax near 'SELECT * FROM bsonarview.nci_nar_units_prev_btv WHERE rel_nar_id IN(47420910,46885004,48321617,48324084,47794973,47907362,481645'. [SQLSTATE 42000] (Error 170). The step failed.

Help! :D

*************************
Got some code from Rob. Can anyone help?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 14:20:05

what's with the null??

IN(' + @rel_nar_id + 'null)'

do this:
WHERE (rel_nar_id IN(' + @rel_nar_id + ') or (rel_nar_id is null))'

as i see it...

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

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-06 : 14:26:26

The null is to trim

DECLARE @sql varchar(8000), @rel_nar_id varchar(8000)

WHILE EXISTS(SELECT * FROM false_churn.dbo.rel_nar_id WHERE Done=1)
BEGIN
SELECT @sql='', @rel_nar_id=''
SELECT TOP 500 @rel_nar_id=@rel_nar_id + cast(rel_nar_id as varchar) + ',' FROM false_churn.dbo.rel_nar_id WHERE Done=1
SET @sql='UPDATE false_churn.dbo.rel_nar_id SET Done=0 WHERE rel_nar_id IN(' + @rel_nar_id + 'null)'
EXEC(@sql)
SET @sql='SELECT * FROM OPENQUERY(ndw, ''SELECT * FROM bsonarview.nci_nar_units_prev_btv WHERE (rel_nar_id IN(' + @rel_nar_id + ') or (rel_nar_id is null))'''
INSERT INTO false_churn.dbo.fc_info2 EXEC(@sql)
END

now i get error message ...
Executed as user: server\userid. Line 1: Incorrect syntax near 'SELECT * FROM bsonarview.nci_nar_units_prev_btv WHERE (rel_nar_id IN(31759258,48433138,47774273,46183624,25339562,47017384,47299'. [SQLSTATE 42000] (Error 170). The step failed.

*************************
Got some code from Rob. Can anyone help?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources