Insert Data in Same table : The Official Microsoft ASP.NET Forums

Reply

mishra.bhupe...

Participant

1598 Points

378 Posts

Insert Data in Same table

Dec 21, 2012 06:03 AM|LINK

I have to write a stored procedure to Insert Data into a table say MYTABLE ,having structure as:

Col1 Col2 Col3 ................ TotalInstallments CurrentInstallment PaidAmount MonthYear

I have to insert all the data as it is in the same table(MYTABLE) except changing some fields on basis of some conditions:

1. if PaidAmount>0 && CurrentInstallment<TotalInstallment then

CurrentInstallment=CurrentInstallment+1

2. In the MonthYear field I am having data in formate(month/year)ex. 01/2012, 11/2012 ....

So I have to insert data by incrementing month and year. for example:

if currentdata is 11/2012 then next data will be 12/2012

But next will be 01/2013

I have to select all the records which belongs to previous month(through MonthYear field ) and put checking & changes on each record of the selected data and then insert them into same table(MYTABLE).

How to achive that?

Thanks.

Edited by mishra.bhupesh on Dec 21, 2012 06:43 AM

Reply

eralper

Contributor

6048 Points

971 Posts

Re: Insert Data in Same table

Dec 21, 2012 07:00 AM|LINK

Hi mishra,

Here is function code you can use to add one month later to given date/year combination

create function addMonth(@s varchar(10)) returns varchar(10)
begin
declare @d as date
set @d = substring(@s,4,4)+ substring(@s,1,2) + '01'
return substring(replace(convert(nvarchar(10),dateadd(mm,1,@d),105),'-','/'),4,7)
end

you will use this sql function in following insert statement

insert into mytable (
col1, col2, col3, 
TotalInstallments, CurrentInstallment, PaidAmount, MonthYear
)
select
col1, col2, col3, 
TotalInstallments,
case when ( PaidAmount > 0 and CurrentInstallment < TotalInstallment ) 
	then CurrentInstallment+1 
	else CurrentInstallment 
end,
CurrentInstallment,
PaidAmount,
dbo.addMonth(MonthYear)
from mytable

Please test above function and insert statement before you use on a productive environment

T-SQL programming SQL Server Tutorials
SQL Server 2012

Marked as answer by mishra.bhupesh on Dec 27, 2012 07:03 AM

Reply

mishra.bhupe...

Participant

1598 Points

378 Posts

Re: Insert Data in Same table

Dec 26, 2012 06:27 AM|LINK

I did it in this way:

CREATE OR REPLACE
PROCEDURE TRANSFERDATATONEXTMONTH(
CUSTOMERID_var IN VARCHAR2,
MONTHYEAR_var IN VARCHAR2,
MESSAGE_var OUT VARCHAR2 )
AS
BEGIN
insert into your_table
(col1, col2, col3...current_installment, MonthYear)
select col1, col2, col3..,
CASE
when paidamount > 0 and current_installment < total_installment then
current_installment +1
else
current_installment
END,
GETMonthYear(MonthYear) where CUSTOMERID=CUSTOMERID_var and MONTHYEAR=MONTHYEAR_var ;

COMMIT;
MESSAGE_VAR:='Success';
END TRANSFERDATATONEXTMONTH;

GETMONTH() is a function which returns the next monthyear body is as following:

create or replace
FUNCTION GETMonthYear(
PAYMONTHYEAR_VAR IN VARCHAR2 )
RETURN VARCHAR2
AS
pmonth VARCHAR(2);
pyear VARCHAR(4);
BEGIN
pmonth :=SUBSTR(PAYMONTHYEAR_VAR,1,2);
pyear :=SUBSTR(PAYMONTHYEAR_VAR,4,4);
IF(pmonth='12') THEN
pmonth:='01';
pyear :=(TO_NUMBER(pyear) +1);
ELSE
pmonth:=TO_NUMBER(pmonth)+1;
if(TO_NUMBER(PMONTH)<10) then
pmonth:='0'||PMONTH;
End if;
END IF;
RETURN pmonth||'/'||pyear;
END GETMonthYear;

Every thing is working fine but some strange result as:
when i run my Procedure TRANSFERDATATONEXTMONTH

1. by Passing Value as : CUSTOMERID_var ='ABX101' and MONTHYEAR_var='12/2012' it insurts 5 rows
which is correct , since I have 5 records where CUSTOMERID='ABX101' and MONTHYEAR='12/2012' and
new 5 rows has CUSTOMERID='ABX101' and MONTHYEAR='01/2013' (all other values are as expected)
2. now when i again run by passing values: CUSTOMERID='ABX101' and MONTHYEAR='01/2013' it inserts 10 records(just double )
and new records has value CUSTOMERID='ABX101' and MONTHYEAR='02/2013' (while on the basis of condition CUSTOMERID='ABX101' and MONTHYEAR='01/2013' i have in my table only 5 records)

and all records are duplicate. Some times it inserts three times , while on condition basis it should no. What is happening?

Why it is inserting double of records while i have only 5 records on given condition? Am I missing some thing?
Please.