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


inserting rows into table using stored procedure


inserting rows into table using stored procedure

Author
Message
mmurawski
mmurawski
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 35
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
Giacomo Degli Esposti
Giacomo Degli Esposti
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 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
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3924 Visits: 8472
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




Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
mmurawski
mmurawski
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 35
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.
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3924 Visits: 8472
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;




Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
KarenM
KarenM
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 524
Since your select returns over 3000 records, your next step would be to test the 'if' condition.



mmurawski
mmurawski
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 35
Hi Cadavre,

For some reason I can't get the code to workSad 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
mmurawski
mmurawski
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 35
Hi Karen,

Thank you - I will test that next!
mmurawski
mmurawski
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 35
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87374 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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