May 15, 2012 at 6:36 am
Hi Everyone,
I am fairly new to sql so please bear with me. I am trying to insert rows from one table into another table using a stored procedure. The sp I'm using was written by an individual who is no longer here. The code is below:
USE [CMSOPEN]
GO
/****** Object: StoredProcedure [dbo].[HM_Annual_Budget_Import] Script Date: 05/14/2012 14:07:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[HM_Annual_Budget_Import] /*Stored Procedure*/
as
set nocount on
declare @EMPL_UNO INT, @MONTH INT, @PERIOD INT, @BILLABLE_HRS MONEY, @NONBILL_HRS MONEY,
@ACCOUNTABLE_HRS MONEY,@BILLABLE_AMT MONEY, @NONBILL_AMT MONEY, @ACCOUNTABLE_AMT MONEY,
@RECEIPT_AMT MONEY, @LAST_MODIFIED DATETIME, @ROW_UNO INT
select @ROW_UNO=lastkey+1 from cms_unique_keys where tbname='tbm_persnl_bud'
declare cur_budget cursor for
select EMPL_UNO, MONTH, PERIOD, BILLABLE_HRS, NONBILL_HRS, ACCOUNTABLE_HRS,
BILLABLE_AMT, NONBILL_AMT, ACCOUNTABLE_AMT, RECEIPT_AMT, LAST_MODIFIED
from _TBM_Persnl_Bud_Update/*Temp Table*/
open cur_budget
fetch from cur_budget into
@EMPL_UNO, @MONTH, @PERIOD, @BILLABLE_HRS, @NONBILL_HRS, @ACCOUNTABLE_HRS,
@BILLABLE_AMT, @NONBILL_AMT, @ACCOUNTABLE_AMT, @RECEIPT_AMT, @LAST_MODIFIED
WHILE (@@FETCH_STATUS = 0)
BEGIN
if not exists(select * from tbm_persnl_bud
where empl_uno=@empl_uno
and month=@MONTH)
begin /* Inserting data into the columns in tbm_persnl_bud*/
insert into tbm_persnl_bud (ROW_UNO,EMPL_UNO, MONTH, PERIOD, BILLABLE_HRS, NONBILL_HRS, ACCOUNTABLE_HRS,
BILLABLE_AMT, NONBILL_AMT, ACCOUNTABLE_AMT, RECEIPT_AMT, LAST_MODIFIED)
values(@ROW_UNO,@EMPL_UNO, @MONTH, @PERIOD, @BILLABLE_HRS, @NONBILL_HRS, @ACCOUNTABLE_HRS,
@BILLABLE_AMT, @NONBILL_AMT, @ACCOUNTABLE_AMT, @RECEIPT_AMT, @LAST_MODIFIED )
set @ROW_UNO=@ROW_UNO+1
end
fetch next from cur_budget into
@EMPL_UNO, @MONTH, @PERIOD, @BILLABLE_HRS, @NONBILL_HRS, @ACCOUNTABLE_HRS,
@BILLABLE_AMT, @NONBILL_AMT, @ACCOUNTABLE_AMT, @RECEIPT_AMT, @LAST_MODIFIED
End --cur_budget
update CMS_UNIQUE_KEYS set lastkey=@ROW_UNO where tbname='tbm_persnl_bud'
CLOSE CUR_Budget
DEALLOCATE CUR_Budget
The only thing I had to change was the temp table since I had to create a new table with the current information. For some reason the insert is not working. The sp executes but 0 rows are inserted. Is there a way to find out what the problem is exactly?
Thanks for any help!
Martina
May 15, 2012 at 6:57 am
Without seeing the DB, I can suggest 2 steps:
Check how many records are returned by the query in the cursor declaration.
If 0 rows are returned then the body of the cursor is never executed so no insert's.
The insert appears inside the body of a 'if' statement.
It could happen that the if condition is false for each row of the cursor.
regards
May 15, 2012 at 7:04 am
First things first, how many rows does this return?
SELECT EMPL_UNO, MONTH, PERIOD, BILLABLE_HRS, NONBILL_HRS, ACCOUNTABLE_HRS,
BILLABLE_AMT, NONBILL_AMT, ACCOUNTABLE_AMT, RECEIPT_AMT, LAST_MODIFIED
FROM _TBM_Persnl_Bud_Update;
If it's 0, then that's your problem as it is what your CURSOR is looping over.
Secondly, why are you using a CURSOR? A CURSOR in this case makes kittens cry :crying: and puppies run away.
Could we look at making it more set-based instead? This is completely untested as you haven't supplied DDL, sample data or expected results. Test it thoroughly before use.
ALTER PROCEDURE [dbo].[HM_Annual_Budget_Import] /*Stored Procedure*/
AS
BEGIN
SELECT @ROW_UNO = lastkey + 1
FROM cms_unique_keys
WHERE tbname = 'tbm_persnl_bud';
MERGE INTO tbm_persnl_bud tbl
USING (SELECT (ROW_NUMBER() OVER(PARTITION BY EMPL_UNO, MONTH ORDER BY (SELECT NULL))-1)+newKey,
EMPL_UNO, MONTH, PERIOD, BILLABLE_HRS, NONBILL_HRS, ACCOUNTABLE_HRS,
BILLABLE_AMT, NONBILL_AMT, ACCOUNTABLE_AMT, RECEIPT_AMT, LAST_MODIFIED
FROM _TBM_Persnl_Bud_Update
CROSS APPLY (SELECT lastkey + 1 AS newKey
FROM cms_unique_keys
WHERE tbname = 'tbm_persnl_bud') b
) correctValues ON (tbl.empl_uno=correctValues.EMPL_UNO AND tbl.month = correctValues.MONTH)
WHEN NOT MATCHED BY TARGET THEN
INSERT (ROW_UNO, EMPL_UNO, MONTH, PERIOD, BILLABLE_HRS, NONBILL_HRS, ACCOUNTABLE_HRS,
BILLABLE_AMT, NONBILL_AMT, ACCOUNTABLE_AMT, RECEIPT_AMT, LAST_MODIFIED)
VALUES (correctValues.newKey, correctValues.EMPL_UNO, correctValues.MONTH, correctValues.PERIOD,
correctValues.BILLABLE_HRS, correctValues.NONBILL_HRS, correctValues.ACCOUNTABLE_HRS,
correctValues.BILLABLE_AMT, correctValues.NONBILL_AMT, correctValues.ACCOUNTABLE_AMT,
correctValues.RECEIPT_AMT, correctValues.LAST_MODIFIED);
SET @ROW_UNO = @ROW_UNO + (CASE WHEN @@ROWCOUNT <> 0 THEN @@ROWCOUNT-1 ELSE 0 END);
UPDATE CMS_UNIQUE_KEYS
SET lastkey = @ROW_UNO
WHERE tbname = 'tbm_persnl_bud';
END
May 15, 2012 at 7:14 am
Hi Cadavre,
The following select statement returns over 3000 rows:
SELECT EMPL_UNO, MONTH, PERIOD, BILLABLE_HRS, NONBILL_HRS, ACCOUNTABLE_HRS,
BILLABLE_AMT, NONBILL_AMT, ACCOUNTABLE_AMT, RECEIPT_AMT, LAST_MODIFIED
FROM _TBM_Persnl_Bud_Update
I'm really not certain why they used a Cursor - as I mentioned, I'm new to sql and I am just using an sp that was already written for the purpose of updating the table.
What I'm trying to accomplish is to insert new rows into an existing table: _Tmp_Persnl_Bud. The rows are :EMPL_UNO, MONTH, PERIOD, BILLABLE_HRS, NONBILL_HRS, ACCOUNTABLE_HRS,
BILLABLE_AMT, NONBILL_AMT, ACCOUNTABLE_AMT, RECEIPT_AMT, LAST_MODIFIED
I created a flat file from an excel spreadsheet that was provided to me with the new information and I created a temp table from that. That is where I'm trying to pull the new information from.
May 15, 2012 at 7:16 am
mmurawski (5/15/2012)
Hi Cadavre,The following select statement returns over 3000 rows:
SELECT EMPL_UNO, MONTH, PERIOD, BILLABLE_HRS, NONBILL_HRS, ACCOUNTABLE_HRS,
BILLABLE_AMT, NONBILL_AMT, ACCOUNTABLE_AMT, RECEIPT_AMT, LAST_MODIFIED
FROM _TBM_Persnl_Bud_Update
I'm really not certain why they used a Cursor - as I mentioned, I'm new to sql and I am just using an sp that was already written for the purpose of updating the table.
What I'm trying to accomplish is to insert new rows into an existing table: _Tmp_Persnl_Bud. The rows are :EMPL_UNO, MONTH, PERIOD, BILLABLE_HRS, NONBILL_HRS, ACCOUNTABLE_HRS,
BILLABLE_AMT, NONBILL_AMT, ACCOUNTABLE_AMT, RECEIPT_AMT, LAST_MODIFIED
I created a flat file from an excel spreadsheet that was provided to me with the new information and I created a temp table from that. That is where I'm trying to pull the new information from.
Did you test the code I posted? It should replicate the CURSOR, but be much quicker.
Also, what is the result of this: -
SELECT COUNT(*)
FROM tbm_persnl_bud a
LEFT OUTER JOIN _TBM_Persnl_Bud_Update b ON a.EMPL_UNO=b.EMPL_UNO AND a.MONTH = b.MONTH
WHERE b.EMPL_UNO IS NULL;
SELECT COUNT(*)
FROM _TBM_Persnl_Bud_Update a
LEFT OUTER JOIN tbm_persnl_bud b ON a.EMPL_UNO=b.EMPL_UNO AND a.MONTH = b.MONTH
WHERE b.EMPL_UNO IS NULL;
May 15, 2012 at 8:01 am
Since your select returns over 3000 records, your next step would be to test the 'if' condition.
May 15, 2012 at 8:06 am
Hi Cadavre,
For some reason I can't get the code to work:( I did run those two select statements.
SELECT COUNT(*)
FROM tbm_persnl_bud a
LEFT OUTER JOIN _TBM_Persnl_Bud_Update b ON a.EMPL_UNO=b.EMPL_UNO AND a.MONTH = b.MONTH
WHERE b.EMPL_UNO IS NULL;
Returns: 6544
and
SELECT COUNT(*)
FROM _TBM_Persnl_Bud_Update a
LEFT OUTER JOIN tbm_persnl_bud b ON a.EMPL_UNO=b.EMPL_UNO AND a.MONTH = b.MONTH
WHERE b.EMPL_UNO IS NULL;
Returns: 3624
May 15, 2012 at 8:07 am
Hi Karen,
Thank you - I will test that next!
May 15, 2012 at 1:43 pm
Hello,
I just wanted to update everyone on this issue. I ended up dropping the temp table and recreating it - that seemed to work and everything imported correctly into the table.
Thank you so much everyone for your help!
Martina
May 15, 2012 at 7:41 pm
mmurawski (5/15/2012)
Hello,I just wanted to update everyone on this issue. I ended up dropping the temp table and recreating it - that seemed to work and everything imported correctly into the table.
Thank you so much everyone for your help!
Martina
If you're still using the cursor or even a While Loop, you still have a performance and resource problem just waiting for you to look the other way.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2012 at 7:00 am
Hi Jeff - I used the current sp for now (with the Cursor) - but I am going to look at the alternative that was posted here for the future. This sp is only run once a year so it wasn't really an issue.
Thank You:)
Martina
May 25, 2012 at 10:44 pm
Just to share a war story on a "once a year" query.
I worked for a company as a consulting DBA. Just after year-end, one of the bosses asked me to build a server and put a copy of the production server data on it. I asked "Why"? His answer was that they ran a "year-end accounting query" once a year and that it would bring the production server to it's knees for about 40 minutes every year starting about 3 years previous. Of course, I fixed the query and got it to run in just a couple of seconds. But that's not the real important part here.
I asked what happend the first year they had a problem. He said that they had a query that always ran just fine and then it "went nuts" that one year. The next year, it also "went nuts" and they had to stop the query and build the machine, copy the data, etc, etc.
The point is that such queries where "performance isn't really a concern" stand a pretty good chance of, suddenly and without any warning, becoming a major concern. Because of the nature of the beast, it ALWAYS happens when you can least afford it to happen.
My recommendation is to at least check the query for possible future scalability problems. If they exist, fix the query now before it's crunch time.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply