Fetch Row by Row with out using cursor

Question

Post reply

Fetch Row by Row with out using cursor

shalini_pdi

Say Hey Kid

Points: 708
More actions
April 29, 2008 at 5:07 am

#181744

Hi,
Could any explain how to fetch row by row with out using cursor.

Viewing 15 posts - 1 through 15 (of 32 total)

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

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

there are a lot of ways to do things without cursors, it depends on what exactly are you trying to do?

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

Hi

I need to fetch the record by each row and get their id and update these id in the other table

For ex:

I have an user table with the following field

User Id name

I am using this user id as a foreign key in the other table employee

i have to copy user details to the same table and get the user id by fetching row by row and performing update function in some other table

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]With the while loop you can do it. To explain well to you, post some live example date.

Mahesh[/font]

MH-09-AM-8694

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 4

To give you a specific answer you need to post specific information, the two tables & some sample data. See here: http://www.sqlservercentral.com/articles/Best+Practices/61537/

In general, you shouldn't have to do cursors or while loops to do batch updates:

UPDATE TableA

SET TableA.Column = b.Column

FROM TableB b

JOIN TableA a

ON a.Id = b.Id

But if you want something specific, you have to provide enough information for people to help out.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Anipaul SSC-Insane Points: 24681 More actions · Answer 5

You can do it in lot of ways. Please tell us in what context you want to fetch data.

🙂

Rob Goddard SSCrazy Points: 2622 More actions · Answer 6

shalini_pdi (4/29/2008)
Hi,
Could any explain how to fetch row by row with out using cursor.

Like everyone has said, you need to give at least demo table structures and what gets updated and when as there are quite a few answers to your question, and most likely it can be done in an update statement.

Right, I'm out of here before Jeff spots the thread... :hehe:

--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

Grant is correct. Tell us what needs to be done... not how to do it. There's usually no need for any form of RBAR. Post some data and the table schema using the methods in the URL he sighted.

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

hi

I have Table name as Recipe

RecipeId Name......

1 Chicken

2 Pizza

and i have a Table as LineItem with the following field

LineItem RecipeId ItemID

1 1 23

2 1 43

3 1 45

4 2 34

5 2 46

6 1 23

I Just want to clone all the recipe in the same recipe table and corresponding line item for the recipe in the same line item table. How to do

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

I would add a placeholder in the recipe table for the "cloned" ID while you're doing this (if you do this a lot - I'd actually leave it in.)

Once you do that - you can actually take care of this fairly easily

So

--testing setup

drop table #recipe

drop table #recipeline

create table #recipe (recipeID int identity(1,1) primary key, recipename varchar(200))

create table #recipeline(reclineID int identity(1,1) primary key, recipeID int, itemid int)

insert #recipe( recipename)

select 'chicken' union all

select 'pizza'

insert #recipeline(recipeid,itemID)

select 1,20 union all

select 1,34 union all

select 1,70 union all

select 1,50 union all

select 2,60 union all

select 2,13 union all

select 2,28

go

alter table #recipe

add clonedID int NULL

go

--start processing

drop table #fun

create table #fun (recipeID int primary key, recipename varchar(200),clonedID int null)

insert #recipe (recipename, clonedid)

output inserted.* into #fun

select recipename,recipeID

from #recipe

insert #recipeline (recipeID,itemID)

select #fun.recipeID, #recipeline.itemID

from #recipeline

inner join #fun on #recipeline.recipeID=#fun.clonedID

update #recipe

set clonedID=null

select * from #recipe

select * from #recipeline

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Without a table alteration, you could do this (using Matt's fine test setup)...

--testing setup

create table #recipe (recipeID int identity(1,1) primary key, recipename varchar(200))

create table #recipeline(reclineID int identity(1,1) primary key, recipeID int, itemid int)

insert #recipe( recipename)

select 'chicken' union all

select 'pizza'

insert #recipeline(recipeid,itemID)

select 1,20 union all

select 1,34 union all

select 1,70 union all

select 1,50 union all

select 2,60 union all

select 2,13 union all

select 2,28

go

--===== Remember the current max recipe ID

DECLARE @MaxRecipeID INT

SELECT @MaxRecipeID = MAX(RecipeID)

FROM #Recipe

--===== Duplicate the rows in the Recipe table

SET IDENTITY_INSERT #Recipe ON

INSERT INTO #Recipe (RecipeID,RecipeName)

SELECT RecipeID = RecipeID + @MaxRecipeID,

RecipeName = RecipeName

FROM #Recipe

SET IDENTITY_INSERT #Recipe OFF

--===== Duplicate the rows in the RecipeLine table

INSERT INTO #RecipeLine(RecipeID,ItemID)

SELECT RecipeID = RecipeID + @MaxRecipeID,

ItemID = ItemID

FROM #RecipeLine

--===== Display the results

SELECT * FROM #Recipe

SELECT * FROM #RecipeLine

--===== Cleanup the demo code

DROP TABLE #Recipe, #RecipeLine

Now, tell us why you want to do this, please? Thanks...

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Shalini - you now have two separate methods which would do what you want. Jeff's doesn't even mess with your initial table, so it's probably the better method (although there are some interesting gotchas if your identity values are all over the place).

Have you actually tried either?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

hi Jeff Moden

Thanks for ur reply the same result i have expected.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 13

Oh... not what I was looking for... I wanted to know why you needed to do this, please...

--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: 1003894 More actions · Answer 14

Rob Goddard (4/29/2008)
shalini_pdi (4/29/2008)
Hi,
Could any explain how to fetch row by row with out using cursor.
Like everyone has said, you need to give at least demo table structures and what gets updated and when as there are quite a few answers to your question, and most likely it can be done in an update statement.
Right, I'm out of here before Jeff spots the thread... :hehe:

Heh... no need to be nervous, Rob... I've not fed anyone a porkchop in, ummmm, at least 2 hours now... 😛

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