|
Like I said I tried using a loop using a status field on the temp table: something like this
while(select top 1 from _customerUpdate where [Status] ='INC') = 'INC'
begin
Begin Try
perform customer update logic here...
End try
Begin Catch
End Catch
Update _CustomerUpdate set Status ='CMP' where CustomerUpdateID = @CustomerUpdateID.
End
Either way both of these ran incredibily slow...
|
|
|
|
|
I don't know your schema so consider this as psudocode, but this will be faster due to the fact that it isn't using a cursor (as suggested previously). Then, if possible, I would add datetime columns which you can use to compare the date your _CustomerUpdate table was changed with the date your CustTreeNodeAccountAssoc table was updated. This will prevent the need to retrieve all 600K+ records, that will also considerably speed things up and it will scale better as the number of records increases.
<br />
Declare @ARbal money,<br />
@CustTreeNodeID uniqueidentifier,<br />
@CustomerNumber varchar(50),<br />
@CustomerUpdateID int,<br />
@LYSales money,<br />
@LYTDSales money,<br />
@YTDgm money,<br />
@YTDSales money,<br />
@MTDSales money,<br />
@LMTDSales money<br />
<br />
Declare @t table<br />
(<br />
CustomerUpdateID int,<br />
CustomerNumber varchar(50),<br />
YTDSales money,<br />
LYSales money,<br />
ARBal money,<br />
LYTDSales money,<br />
MTDSales money,<br />
LMTDSales money<br />
)<br />
<br />
insert into @t(CustomerUpdateID, CustomerNumber,YTDSales, LYSales,ARBal, LYTDsales,MTDSales,LMTDSales)<br />
<br />
select CustomerUpdateID,<br />
CustomerNumber,<br />
YTDSales,<br />
LYSales,<br />
ARBal,<br />
LYTDSales,<br />
MTDSales,<br />
LMTDSales<br />
from _CustomerUpdate u <br />
<br />
where exists (select 1 from CustTreeNode c join CustTreeNodeaccountAssoc ca on ca.CustTreeNodeID = c.CustTreeNodeID and (u.LYTDSales != ca.LYTD or u.YTDSales != ca.YTD or u.MTDSales!= ca.MTD or u.LMTDSales !=ca.LMTD ))<br />
<br />
-- REPLACE YOUR CURSOR WITH THIS CODE BELOW (or something similar that better fits your logic)<br />
UPDATE A<br />
YTD = T.YTDSales, <br />
LYTD = T.LYSales,<br />
Balance = T.ARBal<br />
FROM @t T<br />
INNER JOIN CustTreeNode N ON N.CustomerNumber = T.CustomerNumber<br />
INNER JOIN CustTreeNodeAccountAssoc A ON A.CustTreeNodeID = N.CustTreeNodeID<br />
|
|
|
|
|
Am developing a multi user VB.NET app and i would welcome suggestions on how to code my connection code.
How do i put it such that i only write it once(maybe a module -like i used to do in VB 6) or put it in an XML config file
|
|
|
|
|
Use the config file. If its .NET2 or higher there is a app setting of connectionstring specifically for this.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello,
I have a product table.
I have a ProdIn table with fields (ProdIn,ProdOut,Cost)
ProdIn,ProdOut are the fields from Product Table.
Now I want to fill up ProdIn table with all the combinations of Products.i.e If I have 2 products ,I have 2^2 records in ProdIn.
I don't know how to do this.
I have done this which is wrong
Select Product as ProdIn ,Product as ProdOut from Product
Prithaa
|
|
|
|
|
This will probably do it -
select a.product as prodin,b.product as prodout
from product a, product b
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello,
Thanks
It has worked.
Please give sites which offers such query types.
Prithaa
|
|
|
|
|
No problem.
prithaa wrote: Please give sites which offers such query types
I don't know of any specifically but sqlservercentral is quite good.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How to write a trigger on imported data?
Suppose we import the data on an existing table and we want some updation on imported data automatically by the use of trigger. How should I do?
I am working with sql 2000. I have tried google but no sucess found........ please help me soon...
modified on Thursday, June 12, 2008 2:20 AM
|
|
|
|
|
Just create an on insert trigger, use the special inserted table to get the data you are inserting and update the real table as required.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Bob,
Do you have any other Method?
|
|
|
|
|
Not really. From your original post I would use a trigger, or if it was a one off load maybe run some SQL to do the update after the load, but it all depends on what you are trying to do.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all,
I m working on VC++ 2005, MFC-SDI type application, I m using ODBC for connection of Access Database.
CRecordset use as Derived class of Database.
Here i want to get the index of Currently save Record.
Please tell me how can i do this.
I m waiting for your valuable suggestions.
Thanks in advance.
IN A DAY, WHEN YOU DON'T COME ACROSS ANY PROBLEMS - YOU CAN BE SURE THAT YOU ARE TRAVELLING IN A WRONG PATH
|
|
|
|
|
|
I am currently using a sproc like the one below to get both a dataset and a count of the rows returned.
Is there a better way to do this?
@Filter int,
@theRows int output
AS
BEGIN
SELECT
Field1,
Field2
FROM
MyTable
WHERE
FilterField=@Filter
SET @theRows=(select count(*) from MyTable WHERE FilterField=@Filter)
END
Thanks.
|
|
|
|
|
Try this
@Filter int, @theRows int output
AS
BEGIN
SELECT Field1, Field2
FROM MyTable
WHERE FilterField=@Filter
SELECT @theRows = @@ROWCOUNT
END
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I knew there was a better way than doing another select/from
Thanks Bob.
|
|
|
|
|
No problem.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi..
Sure You Can Have This One....
@Filter int,
@theRows int output
AS
BEGIN
SET @theRows=(SELECT Count(Field)
FROM MyTable
WHERE FilterField=@Filter)
END
Hope It Will work...
do Reply..
Have Nice Day..
|
|
|
|
|
After creating table what next? -
|
|
|
|
|
Wow - you had to ask this twice ? And you've still not asked anything. What next to do what exactly ?
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Thank you for the link, I got valuable information there. Please, encourage me the more.
|
|
|
|
|
can i have some books? please do
|
|
|
|
|
Heh - the "quality" of questions sometimes is staggering......
|
|
|
|
|
safealloys wrote: After creating table what next? -
Drop it. Go Home. Eat and Sleep.
When you can not form a complete sentence correctly and can only crop-dust the forum multiple times with the same crap as here (http://www.codeproject.com/script/Forums/View.aspx?fid=1725&msg=2593420[^]), that is what you can do.
Vasudevan Deepak Kumar
Personal Homepage Tech Gossips
All the world's a stage,
And all the men and women merely players.
They have their exits and their entrances;
And one man in his time plays many parts... --William Shakespeare
|
|
|
|