Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting multiple rows using stored procedure Expand / Collapse
Author
Message
Posted Wednesday, July 8, 2009 4:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 5:14 AM
Points: 15, Visits: 145
Hi All,

Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)

Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)

The first table is used to store the token starting number and end number.




for example (staring number)1 to 100(ending number). so i have to insert token1,token2...token100 in tab_Token_details table.



how can i insert that number of records using stored procedure?
is there for loop to do this task?
plz help me in this issue?
thnks in advance







Post #749136
Posted Wednesday, July 8, 2009 5:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 AM
Points: 6,600, Visits: 8,900
karthimca07 (7/8/2009)
Hi All,
Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)

Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)

The first table is used to store the token starting number and end number.



for example (staring number)1 to 100(ending number). so i have to insert token1,token2...token100 in tab_Token_details table.



how can i insert that number of records using stored procedure?
is there for loop to do this task?
plz help me in this issue?
thnks in advance


declare @Transid int, @TokenStart int, @TokenEnd int, @TokenValue int
insert into dbo.Tab_Token_Details (Transid, Token_No, Token_Value, Token_Status)
select
@Transid,
N,
@TokenValue,
'A' -- or whatever your status field should be
from dbo.Tally
where N between @TokenStart and @TokenEnd

If you don't have a tally table, search this site... it's explained and used frequently.


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #749160
Posted Wednesday, July 8, 2009 6:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 5:14 AM
Points: 15, Visits: 145
WayneS (7/8/2009)
karthimca07 (7/8/2009)
Hi All,
Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)

Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)

The first table is used to store the token starting number and end number.



for example (staring number)1 to 100(ending number). so i have to insert token1,token2...token100 in tab_Token_details table.



how can i insert that number of records using stored procedure?
is there for loop to do this task?
plz help me in this issue?
thnks in advance


declare @Transid int, @TokenStart int, @TokenEnd int, @TokenValue int
insert into dbo.Tab_Token_Details (Transid, Token_No, Token_Value, Token_Status)
select
@Transid,
N,
@TokenValue,
'A' -- or whatever your status field should be
from dbo.Tally
where N between @TokenStart and @TokenEnd

If you don't have a tally table, search this site... it's explained and used frequently.





the first table Tab_Token_Master is used to store the token generation and the second table used to store the all generated tokens.




Tab_Token_Master
-----------------------------------------------------------------
Transid Token_starts_from Token_Ends_To Token_value
-----------------------------------------------------------------
trans01 101 105 100
------------------------------------------------------------------

Tab_Token_Details
-----------------------------------------------------------------
Transid Token_No Token_Value Token_Status
-----------------------------------------------------------------
trans01 101 100 0
trans01 102 100 1
trans01 103 100 0
trans01 104 100 1
trans01 105 100 1
-----------------------------------------------------------------

in the above example data 5 tokens generated. im calling insert statement in a for loop in vb.net. it become very slow. so im looking for stored procedure


Post #749176
Posted Wednesday, July 8, 2009 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 11,297, Visits: 13,083
How do you know what to set token_status to in Tab_Token_Details?

Is you current process doing an insert into Tab_Token_Master, then looping through and inserting the correct number of rows into Tab_Token_Details or are you reading Tab_Token_Master and then inserting into Tab_Token_Details?

For the first scenario you could do it in one stored procedure like this:

CREATE PROCEDURE create_tokens 
(
@transid VARCHAR(10),
@token_starts_from SMALLINT,
@tokens_ends_to SMALLINT,
@token_value SMALLINT
)
AS

BEGIN TRANSACTION token_master

INSERT INTO Tab_Token_Master
(
transid,
token_starts_from,
token_ends_to,
token_value
)
VALUES
(
@transid,
@token_starts_from,
@token_ends_to,
@token_value
)
COMMIT TRANSACTION token_master

BEGIN TRANSACTION token_details
-- uses WayneS's code so you still need to lookup tally table
insert into dbo.Tab_Token_Details (Transid, Token_No, Token_Value, Token_Status)
select
@Transid,
N,
@token_value ,
'A' -- or whatever your status field should be
from
dbo.Tally
where N between @token_starts_from AND @tokens_ends_to
COMMIT TRANSACTION token_details

RETURN

For the second scenario you just need to remove the insertion into Tab_Token_Master




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #749378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse