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
 cursor question

Author  Topic 

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-29 : 08:18:02
i have a stored procedure that is taking forever to run.

it joins 5 tables and returns 5k results which i then need to insert into another table and then delete from another table

couldnt think of a way to do it without a cursor

what im worried about is whether its running the query over and over again -- can someone confirm its not or tell me a quicker way to do it

have the following:

Declare c1 CURSOR FOR
SELECT bunch of stuff FROM mytable

INNER JOIN
INNER JOIN etc

get 5k results

OPEN c1

Declare variable

FETCH NEXT FROM c1

INTO @variable

WHILE @@FETCH_STATUS = 0

BEGIN
insert statement
delete statement
END

CLOSE c1
DEALLOCATE c1
GO

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-29 : 08:22:51
to run the query without the insert delete takes a 0.01 s

its run for 10 min with the inserts
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-29 : 08:33:13
Why you need cursor for this? Why not do it directly?

Insert into DestTable(col1, col2, col3, ...)
Select col1, col2, col3,.. from SourcTable
Join Table2 on ...
Join Tabl3 on ...


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-29 : 09:04:42
had that originally -- couldnt figure out how to get the delete statement to work with the insert and the select as the values
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-29 : 09:08:29
Post proper INSERT and SELECT statement here and we can provide some help.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-01-02 : 09:44:29
what i want to do is this

insert into table_insert(value1, value2)
(select value1, value2 from table_exclude)

delete from table_exclude where id = "the id from the select of the insert statement)

basically i want to do an insert and then delete the record from the tablethe select statement came from
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-02 : 09:51:16
You can temporarily insert IDs of the data to be inserted in the temporary table and make use of temporary table in DELETE statement.

create table #temp
(
[ID] int
)

Insert #temp
select [ID] from table_exclude Where ...

insert into table_insert(value1, value2)
(select value1, value2 from table_exclude Where ...)

Delete E
From table_exclude E Join #Temp T
on E.[ID] = T.[ID]

Drop table #temp


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-01-02 : 10:42:56
If you want to be sure that both, or neither, work you need something like:

create table #temp
(
[ID] int
)

Insert #temp
select [ID] from table_exclude Where ...

BEGIN TRANSACTION
insert into table_insert(value1, value2)
(select value1, value2 from table_exclude Where ...)
IF @@ERROR <> 0 GOTO GotError
Delete E
From table_exclude E Join #Temp T
on E.[ID] = T.[ID]
IF @@ERROR <> 0 GOTO GotError
COMMIT
GOTO DoneTrans

GotError:
ROLLBACK

DoneTrans:


Drop table #temp

Kristen
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-01-02 : 12:48:20
doesnt that run the same query twice? one to get the temp table and once to get the id values
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-01-02 : 13:07:56
Yes - personally I would get all the data I needed into #TEMP, use that for the INSERT, and then JOIN #Temp to table_exclude for the DELETE.

The issue that I expect Harsh is addressing is to be sure that all the data / identities for the INSERT and the DELETE are the same - and NOT possibly variable in case something else has inserted some new rows into table_exclude between the INSERT and the DELETE - ensuring that an identical set of data is INSERTed into table_insert as is DELETEd from table_exclude

Kristen
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-01-05 : 09:59:11
that got it working thanks
Go to Top of Page
   

- Advertisement -