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 tablecouldnt think of a way to do it without a cursorwhat 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 ithave the following:Declare c1 CURSOR FORSELECT bunch of stuff FROM mytableINNER JOIN INNER JOIN etcget 5k resultsOPEN c1Declare variableFETCH NEXT FROM c1INTO @variable WHILE @@FETCH_STATUS = 0 BEGIN insert statement delete statement ENDCLOSE c1DEALLOCATE c1GO |
|
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 sits run for 10 min with the inserts |
|
|
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 SourcTableJoin Table2 on ...Join Tabl3 on ... Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
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 |
|
|
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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 2007-01-02 : 09:44:29
|
what i want to do is thisinsert 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 |
|
|
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 #tempselect [ID] from table_exclude Where ...insert into table_insert(value1, value2)(select value1, value2 from table_exclude Where ...)Delete EFrom table_exclude E Join #Temp Ton E.[ID] = T.[ID] Drop table #temp Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
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 #tempselect [ID] from table_exclude Where ...BEGIN TRANSACTIONinsert into table_insert(value1, value2)(select value1, value2 from table_exclude Where ...)IF @@ERROR <> 0 GOTO GotErrorDelete EFrom table_exclude E Join #Temp Ton E.[ID] = T.[ID]IF @@ERROR <> 0 GOTO GotErrorCOMMIT GOTO DoneTransGotError:ROLLBACKDoneTrans:Drop table #temp Kristen |
|
|
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 |
|
|
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_excludeKristen |
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 2007-01-05 : 09:59:11
|
that got it working thanks |
|
|
|