Fetch Row by Row with out using cursor

Question

Fetch Row by Row with out using cursor

Viewing 3 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

shalini_pdi (5/5/2008)
I have followed the same procedure as u have mentioned. Its working fine. If i execute the code more than once i got the error as There is already an object named '#Clone' in the database.So i have changed the '#Clone' in to different name when each and every time it executes.

Not quite the way to do it... if you convert the code to a stored procedure and run it, #Clone will drop itself at the completion of the stored procedure, automatically. You're trying to run it more than once as a simple script in SMS... and you need to forcibly drop #Clone for additional runs. Here's the modified code that does so--================================================================================

-- Clone recipes by location

--================================================================================

--===== Presets

SET NOCOUNT ON --Suppress the auto-display of rowcounts for appearance

SET XACT_ABORT ON --Stop processing if anything goes wrong in the transaction

--===== If the clone table already exists, drop it...

IF OBJECT_ID('TempDB..#Clone','U') IS NOT NULL

DROP TABLE #Clone

--===== Declare the local variables

DECLARE @LocationSource INT --The location we want to clone

DECLARE @LocationTarget INT --The newly cloned location

--===== Identify the loaction numbers involved in the cloning

SET @LocationSource = 1

SET @LocationTarget = 4

--===== Start a transaction because this all has to happen together

BEGIN TRANSACTION

--===== Get ALL the information we need at once including the new recipe ID's

;WITH

cteKnowAll AS

(

SELECT RecipeNumber = DENSE_RANK() OVER (ORDER BY r.RecipeID),

OldRecipeID = r.RecipeID,

r.RecipeName,

OldLocationID = r.LocationID,

NewLocationID = @LocationTarget,

i.ItemID

FROM #Recipe r WITH (TABLOCKX), --Lock the table so no one can insert/update/delete

#RecipeLineItem i WITH (TABLOCKX) --Lock the table so no one can insert/update/delete

WHERE r.RecipeID = i.RecipeID

AND r.LocationID = @LocationSource

)

SELECT NewRecipeID = k.RecipeNumber+IDENT_CURRENT('#Recipe'),

RecipeName = k.RecipeName,

NewLocationID = k.NewLocationID,

ItemID = k.ItemID

INTO #Clone

FROM cteKnowAll k

--===== Clone the recipes

SET IDENTITY_INSERT #Recipe ON

INSERT INTO #Recipe

(RecipeID,RecipeName,LocationID)

SELECT DISTINCT

NewRecipeID,RecipeName,NewLocationID

FROM #Clone

SET IDENTITY_INSERT #Recipe ON

--===== Clone the recipe line items

INSERT INTO #RecipeLineItem

(RecipeID,ItemID)

SELECT DISTINCT NewRecipeID,ItemID

FROM #Clone

--===== If we made it to here, we can commit

COMMIT

--===== Verify the original condition of the test tables

SELECT * FROM #Recipe

SELECT * FROM #RecipeLineItem

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shalini_pdi Say Hey Kid Points: 708 More actions · Answer 2

Hi Jeff,

Thanks a lot, Working Perfectly after converting in to stored procedure

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Thank you for the feedback 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)