InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 23:41:56 GMT20http://www.sqlservercentral.com/Forums/Topic749136-9-1.aspxHow 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:[code]CREATE PROCEDURE create_tokens ( @transid VARCHAR(10), @token_starts_from SMALLINT, @tokens_ends_to SMALLINT, @token_value SMALLINT )ASBEGIN TRANSACTION token_masterINSERT 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_masterBEGIN 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[/code]For the second scenario you just need to remove the insertion into Tab_Token_MasterWed, 08 Jul 2009 09:11:25 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic749136-9-1.aspx[quote][b]WayneS (7/8/2009)[/b][hr][quote][b]karthimca07 (7/8/2009)[/b][hr]Hi All,[code]Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)[/code][code]Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)[/code] [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][/quote][code]declare @Transid int, @TokenStart int, @TokenEnd int, @TokenValue intinsert 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[/code]If you don't have a tally table, search this site... it's explained and used frequently.[/quote][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 0trans01 102 100 1trans01 103 100 0trans01 104 100 1trans01 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 procedureWed, 08 Jul 2009 06:10:14 GMTkarthimca07http://www.sqlservercentral.com/Forums/Topic749136-9-1.aspx[quote][b]karthimca07 (7/8/2009)[/b][hr]Hi All,[code]Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)[/code][code]Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)[/code] [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][/quote][code]declare @Transid int, @TokenStart int, @TokenEnd int, @TokenValue intinsert 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[/code]If you don't have a tally table, search this site... it's explained and used frequently.Wed, 08 Jul 2009 05:37:56 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic749136-9-1.aspxHi All,[code]Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)[/code][code]Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)[/code] [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]Wed, 08 Jul 2009 04:49:17 GMTkarthimca07