Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

inserting rows into table using stored procedure Expand / Collapse
Author
Message
Posted Tuesday, May 15, 2012 6:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:07 PM
Points: 8, Visits: 34
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
Post #1300170
Posted Tuesday, May 15, 2012 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 30, 2014 2:45 AM
Points: 121, Visits: 142
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




Post #1300183
Posted Tuesday, May 15, 2012 7:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:22 AM
Points: 2,422, Visits: 7,436
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 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




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1300185
Posted Tuesday, May 15, 2012 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:07 PM
Points: 8, Visits: 34
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.
Post #1300193
Posted Tuesday, May 15, 2012 7:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:22 AM
Points: 2,422, Visits: 7,436
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;




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1300197
Posted Tuesday, May 15, 2012 8:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:01 AM
Points: 256, Visits: 477
Since your select returns over 3000 records, your next step would be to test the 'if' condition.


Post #1300249
Posted Tuesday, May 15, 2012 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:07 PM
Points: 8, Visits: 34
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
Post #1300258
Posted Tuesday, May 15, 2012 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:07 PM
Points: 8, Visits: 34
Hi Karen,

Thank you - I will test that next!
Post #1300259
Posted Tuesday, May 15, 2012 1:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:07 PM
Points: 8, Visits: 34
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
Post #1300563
Posted Tuesday, May 15, 2012 7:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1300713
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse