February 2, 2011 at 6:12 pm
Hi All,
I've got some data to insert from my .net app to the database.
I think insert all the data to table and filter all in once, is better than insert one row and filter one.
so,there are the steps:
1. the app calls a simple insert procedure to insert all of data to the table in database 1st.
2. nxt step, the app calls the second procedure to filter the rows, and put those rows into the orginal table
I want the app only calls the 1st step, when the data all goes into database and do the 2nd step itself, without calling the 2nd procedure from the app.
1st SP
ALTER procedure [usp_insert_NewRow] (
@dateCall,
@phoneNumber as int,
@firstName as nchar(30),
@lastName as nchar(30),
@address1 as nchar(20),
@address2 as nchar(30),
@address3 as nchar(30),
@myRowCount as int output
)
AS
INSERT INTO temp_tblNewRow(
dataCall,
phoneNumber,
firstName,
lastName,
address1,
address2,
address3
)
VALUES(
@dateCall,
@phoneNumber,
@firstName,
@lastName,
@address1,
@address2,
@address3
)
select @myRowCount=@@rowcount;
create procedure [usp_insert_to_orgTbl]
(@myRowCount as int output)
as
declare @newNumber table(
datCall datatime,
phoneNumber int,
firstName nchar(30),
lastName nchar(30),
address1 nchar(20),
address2 nchar(30),
address3 nchar(30),
)
insert into @newNumber (dateCall, phoneNumber, firstName, lastName, address1,
address2, address3)
select dateCall, phoneNumber, firstName, lastName, address1,
address2, address3
from temp_tblNewrow r
where not exists (select 1 from tbl_org
where phoneNumber=r.phoneNumber
and dateCall=r.dateCall)
insert into tbl_org (dateCall, phoneNumber, firstName, lastName, address1, address2, address3)
select dateCall, phoneNumber, firstName, lastName, address1,
address2, address3
from @newNumber
set @myRowCount=@@rowcount
select @myRowCount
my question is:
1. should I use #tempTable instead of a normal table to store the data on the 1st step?
2. how can I call the 2nd step after the 1st step, in the same procedure?
3. Or, any other way that can achieve the same thing?
February 2, 2011 at 6:42 pm
In both procedures I would use the TRY CATCH technique, first to report any errors, and secondly if the first SP generates an error the 2nd SP would not be activated. Reason - if the first fails during processing of the input data, if the 2nd SP ran it would be a mess trying to correct the partial input.
Yes you can invoke the 2nd SP from within the 1st SP using the execute command. For example:
DECLARE @proc_name varchar(30);
SET @proc_name = 'sys.sp_who';
EXEC @proc_name;
February 2, 2011 at 9:05 pm
my 1st procedure will repeat many time, until all rows been insert to a table.
if I put the 2nd procedure into 1st, how to know when the last row is, cuz 2nd procedure need to wait until all 1st procedure(s) done
February 3, 2011 at 2:56 am
What about a flag on the 1st proc to indicate if you are finished?
What about calling the 2nd proc from the client?
Are you also making sure that the temp table will be unique enough for the 2nd proc. You do not want multiple users polluting each others inserts. (you are doing it, I am just commenting if it is unique enough)
February 3, 2011 at 3:29 am
Maybe you could set up a trigger to do the second part of the processing during the inserts.
February 3, 2011 at 3:47 am
With a trigger you would not be able to have a transaction when inserting into the final table, as it will effectively insert it one row at a time as ou insert into the temp table.
So if row 5 fails, rows 1 - 4 will still be inserted.
February 3, 2011 at 8:05 am
dlam 18073 (2/2/2011)
my 1st procedure will repeat many time, until all rows been insert to a table.if I put the 2nd procedure into 1st, how to know when the last row is, cuz 2nd procedure need to wait until all 1st procedure(s) done
If I understand you correctly, the first procedure will be called many times, and each time it is invoked the 2nd procedure will be invoked, insert the one row and then stop. Then the 1st procedure is called, and it in turn calls the 2nd procedure. This sequence continues until all rows are processed.
Now this technique is called RBAR (Row by agonizing row). I do not know what your interface is, i.e. that process which is invoking the 1st procedure, but if you post more information I am willing to say that someone will devise a set based method for your use.
February 3, 2011 at 3:33 pm
Iam using datatable from .net app connect to database,
once click [ok] the app calls a store procedure(the 1st step),
to insert all the rows to a table in the database
(one of my qs from the top, should I use a #temp table, or normal table,
if more than one user uses that normal table, it might get different user's input,
what if I use #temp table, is this will happen the same thing),
once the app reply the 1st step done, the app calls the 2nd procedure to input all the rows to the orginal table.<< that is what I am doing.
but what I want this found the way to detect all the 1st steps done, then call the 2nd sp itself.
I know if I put the 2nd sp within the 1st one, the 2nd one will be call many time as the 1st sp<< I dont want it happen
February 3, 2011 at 4:17 pm
what if I use #temp table, is this will happen the same thing),
No it will not happen - Read this from BOL (Books On Line - the help file for SQL Server):
If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, the Database Engine must be able to distinguish the tables created by the different users. The Database Engine does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply