Fetch Row by Row with out using cursor

Question

Post reply

Fetch Row by Row with out using cursor

Viewing 15 posts - 16 through 30 (of 32 total)

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

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

hi Jeff

This is to implement some cloning functionality in my application

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 2

Heh... Yeaup... I know that... what I want to know is why you need to clone rows like this...

--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 3

Hi jeff

This is just like copying the recipes and its corresponding lineitem from one location to other location to reduce the time of dataentry for the user to maintain in their application

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 4

shalini_pdi (4/29/2008)
from one location to other location to reduce the time of dataentry

Perfect... that's what I was looking for. Thank you so much.

--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)

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 5

Oh, crud... I just thought of this... If you're using it for that, it's possible that two people could conflict with each other and get the wrong rows...

We've gotta come up with something else unless you're doing this in a batch job instead of by individuals... that's another reason why I ask... make sure we haven't given you the wrong answer for what you're doing. We may have to throw a transaction into this...

--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)

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 6

The other thing to be considered, it what the new rows are in the RecipeLine table are... you'll need to pass those ID's back to the GUI, won't you?

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

Hi,

I will get these id in the gui.I have prob with the query bcoz if i give some thing like this SELECT @MaxRecipeId = MAX(RecipeId)

FROM Recipe Where LocationId =2

I am getting the error as Violation of PRIMARY KEY constraint

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

Hi Jeff

I m getting error when i get the query based on some condition as i have mentioned in previous post

steveb. SSC-Forever Points: 46733 More actions · Answer 9

You must be getting this error when inserting the data into a table,

are you able to post the procedure that you use to insert the data?

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 10

shalini_pdi (5/2/2008)
Hi Jeff
I m getting error when i get the query based on some condition as i have mentioned in previous post
"I am getting the error as Violation of PRIMARY KEY constraint"

Real hard to figure out why with no code, no data, no table structure. Obviously, you'll need to change your logic as SteveB suggested... stop inserting dupes is what it boils down to.

--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 11

Hi jeff

Consider the table structure would be

RecipeId RecipeName LocationId

1 chicken 1

2 Pizza 1

3 chicken Breasts 2

RecipeLineItem

RecipeLineItemId RecipeId ItemId

1 1 20

2 1 21

3 1 22

4 2 20

5 2 24

DECLARE @MaxRecipeId as INT

SELECT @MaxRecipeId = MAX(RecipeId )

FROM Recipe Where LocationId =1

SET IDENTITY_INSERT Recipe ON

INSERT INTO Recipe (RecipeId,[Name],LocationId)

SELECT RecipeId = RecipeId + @MaxRecipeId,

[Name] = [Name],

LocationId = 4,

FROM Recipe Where LocationId = 1

SET IDENTITY_INSERT Recipe OFF

INSERT INTO RecipeLineItem(RecipeId,ItemId)

SELECT RecipeId = RecipeId + @MaxRecipeId,

ItemId = ItemId

FROM RecipeLineItem

I have used the following query.In the table structure recipeId and RecipeLineItem are primary key.

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 12

I haven't tried it, but I believe your problem is in the following code...

DECLARE @MaxRecipeId as INT

SELECT @MaxRecipeId = MAX(RecipeId )

FROM Recipe Where LocationId =1

It should not be limited to a given location ID... Remove the WHERE clause and you should be all set.

--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 13

hi jeff

As of now my requirement is i have to clone the item based on the locationid. so i have insert the recipe and recipelineItem based on the location.When i remove the where clause i would not meet the requirement

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 14

First, take a look at the URL in my signature to get better help, faster. 😉

Following my own "rules", here some test tables and some test data...

--===== Presets

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

--===== Create some temporary test tables and populate them with test data

--drop table #Recipe,#RecipeLineItem,#Clone

CREATE TABLE #Recipe

(RecipeID INT IDENTITY(1,1) PRIMARY KEY, RecipeName VARCHAR(30), LocationID INT)

INSERT INTO #Recipe (RecipeName, LocationID)

SELECT 'Chicken' ,1 UNION ALL

SELECT 'Pizza' ,2 UNION ALL

SELECT 'Chicken Breasts',1

CREATE TABLE #RecipeLineItem

(RecipeLineItemId INT IDENTITY(1,1) PRIMARY KEY,RecipeId INT,ItemId INT)

INSERT INTO #RecipeLineItem (RecipeID,ItemID)

SELECT 1,20 UNION ALL

SELECT 1,21 UNION ALL

SELECT 1,22 UNION ALL

SELECT 2,20 UNION ALL

SELECT 2,24 UNION ALL

SELECT 3,13

... and, loving SQL Server 2005 a bit more than I used to, here's your solution... the key, in this case, was to either do it using RBAR (not on my watch!) or you had to know EVERYTHING you were going to insert before the actual INSERT. Obviously, I chose the latter. As always, the explanations are commented in the code.

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

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

--===== 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 15

Jeff Moden (5/3/2008)
First, take a look at the URL in my signature to get better help, faster. 😉
Following my own "rules", here some test tables and some test data...
--===== Presets
SET NOCOUNT ON --Suppress the auto-display of rowcounts for appearance
--===== Create some temporary test tables and populate them with test data
--drop table #Recipe,#RecipeLineItem,#Clone
CREATE TABLE #Recipe
(RecipeID INT IDENTITY(1,1) PRIMARY KEY, RecipeName VARCHAR(30), LocationID INT)
INSERT INTO #Recipe (RecipeName, LocationID)
SELECT 'Chicken' ,1 UNION ALL
SELECT 'Pizza' ,2 UNION ALL
SELECT 'Chicken Breasts',1
CREATE TABLE #RecipeLineItem
(RecipeLineItemId INT IDENTITY(1,1) PRIMARY KEY,RecipeId INT,ItemId INT)
INSERT INTO #RecipeLineItem (RecipeID,ItemID)
SELECT 1,20 UNION ALL
SELECT 1,21 UNION ALL
SELECT 1,22 UNION ALL
SELECT 2,20 UNION ALL
SELECT 2,24 UNION ALL
SELECT 3,13
... and, loving SQL Server 2005 a bit more than I used to, here's your solution... the key, in this case, was to either do it using RBAR (not on my watch!) or you had to know EVERYTHING you were going to insert before the actual INSERT. Obviously, I chose the latter. As always, the explanations are commented in the code.
--================================================================================
-- 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
--===== 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

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.