What is the best approach than cursor in the following scenario

Question

What is the best approach than cursor in the following scenario

vijay-367524

SSC-Addicted

Points: 481
More actions
September 1, 2010 at 11:01 am

#387714

I have two temp tables with parent child relationship. i have to insert the data from these tables to original tables and perform some calculations. For this currently iam using cursor. I want to know whether there is any best approach to achieve this functionality.
Iam attaching the sqlschema and some sample data for your reference
Thanks,
Vijay

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

The attached script is mangled when I open it. Looks like line-breaks were removed.

You can almost always insert from temp tables to real tables with simple Insert...Select statements, and perform the calculations in the Select.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WayneS SSC Guru Points: 95460 More actions · Answer 2

I assume that you are using a cursor so that you can get the value of the identity column [dbo].[StockTransferNote].[WebSTNNo], so that it can be put into [dbo].[StnXItems].[WebSTNNo]?

In this case, you probably want to look into the OUTPUT clause of the INSERT statement. Put your data into the StockTransferNote table, output the WebSTNNo and whatever other columns you need to associate the record back in the temp table, then insert into the other table joining on this temp table.

Something like this:

declare @StockTransferNoteOut TABLE (WebSTNNo int, StnNo varchar(50));

declare @StockTransferNote TABLE (WebSTNNO INT IDENTITY, StnNo varchar(50));

declare @Stn_Temp TABLE (AutoID INT IDENTITY, StnNo varchar(50));

declare @STN_TempXItems TABLE (StnLineNo int, StnNo varchar(50));

declare @StnXItems TABLE (

[WebSTNLineItemId] [int] IDENTITY(1,1) NOT NULL,

[WebSTNNo] [int],

[SAPSTNNo] [varchar](500),

[LineNumber] [varchar](50));

INSERT INTO @Stn_Temp values ('0090');

INSERT INTO @STN_TempXItems values (1, '0090'), (2, '0090');

INSERT INTO @StockTransferNote (StnNo)

OUTPUT inserted.WebSTNNO, inserted.StnNo

INTO @StockTransferNoteOut

SELECT StnNo

FROM @Stn_Temp;

INSERT INTO @StnXItems (WebSTNNo, LineNumber, SAPSTNNo)

SELECT stno.WebSTNNo,

stxi.StnLineNo,

stxi.StnNo

FROM @StockTransferNoteOut stno

JOIN @STN_TempXItems stxi

ON stno.StnNo = stxi.StnNo;

select * from @StockTransferNote;

select * from @StnXItems;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

vijay-367524 SSC-Addicted Points: 481 More actions · Answer 3

I assume that you are using a cursor so that you can get the value of the identity column [dbo].[StockTransferNote].[WebSTNNo], so that it can be put into [dbo].[StnXItems].[WebSTNNo]?

Yes,You are correct. Iam writing cursor to fetch records from [dbo].[Stn_Temp] table and for each record iam also getting records from [dbo].[STN_TempXItems] and first inserting [Stn_Temp] record into [dbo].[StockTransferNote] table and then inserting [dbo].[StockTransferNote].[WebSTNNo] and [dbo].[STN_TempXItems] into [dbo].[StnXItems]

In this case, you probably want to look into the OUTPUT clause of the INSERT statement. Put your data into the StockTransferNote table, output the WebSTNNo and whatever other columns you need to associate the record back in the temp table, then insert into the other table joining on this temp table.

Till now i don't know about this OUTPUT clause. I think this will workout. Anyway i will try this and get back to you.

Thank you for providing the solution.

Thanks,

Vijay

WayneS SSC Guru Points: 95460 More actions · Answer 4

vijay-367524 (9/5/2010)
I assume that you are using a cursor so that you can get the value of the identity column [dbo].[StockTransferNote].[WebSTNNo], so that it can be put into [dbo].[StnXItems].[WebSTNNo]?
Yes,You are correct. Iam writing cursor to fetch records from [dbo].[Stn_Temp] table and for each record iam also getting records from [dbo].[STN_TempXItems] and first inserting [Stn_Temp] record into [dbo].[StockTransferNote] table and then inserting [dbo].[StockTransferNote].[WebSTNNo] and [dbo].[STN_TempXItems] into [dbo].[StnXItems]
In this case, you probably want to look into the OUTPUT clause of the INSERT statement. Put your data into the StockTransferNote table, output the WebSTNNo and whatever other columns you need to associate the record back in the temp table, then insert into the other table joining on this temp table.
Till now i don't know about this OUTPUT clause. I think this will workout. Anyway i will try this and get back to you.
Thank you for providing the solution.
Thanks,
Vijay

You're welcome. And please don't forget to get back to us and let us know how it goes.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2