July 8, 2009 at 4:49 am
Hi All,
Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)
Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)
[p]The first table is used to store the token starting number and end number.[/p]
[p]for example (staring number)1 to 100(ending number). so i have to insert token1,token2...token100 in tab_Token_details table.[/p]
[p]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[/p]
July 8, 2009 at 5:37 am
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)[p]The first table is used to store the token starting number and end number.[/p]
[p]for example (staring number)1 to 100(ending number). so i have to insert token1,token2...token100 in tab_Token_details table.[/p]
[p]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[/p]
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
Author - SQL Server T-SQL Recipes
July 8, 2009 at 6:10 am
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)[p]The first table is used to store the token starting number and end number.[/p]
[p]for example (staring number)1 to 100(ending number). so i have to insert token1,token2...token100 in tab_Token_details table.[/p]
[p]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[/p]
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.
[p]the first table Tab_Token_Master is used to store the token generation and the second table used to store the all generated tokens.[/p]
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
July 8, 2009 at 9:11 am
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
Consultant - Straight Path Solutions
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply