SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserting multiple rows using stored procedure


Inserting multiple rows using stored procedure

Author
Message
karthimca07
karthimca07
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 152
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


WayneS
WayneS
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22024 Visits: 10656
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
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, 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

karthimca07
karthimca07
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 152
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45561 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search