declare cur CURSOR LOCAL for select account_name,company_ID,account_number,is_group,root_type,report_type,account_type,tax_rate,freeze_account,
balance_must_be,parent_account_ID,account_currency_ID
from Account3
open cur
fetch next from cur into
@account_name,
@account_number ,
@is_group,
@root_type ,
@report_type,
@account_type,
@tax_rate,
@freeze_account,
@balance_must_be,
@parent_account_ID,
@account_currency_ID,
@company_ID
while @@FETCH_STATUS = 0 BEGIN
--execute your sproc on each row
exec [dbo].[INSERT_Account_empty]
fetch next from cur into
@account_name,
@account_number ,
@is_group,
@root_type ,
@report_type,
@account_type,
@tax_rate,
@freeze_account,
@balance_must_be,
@parent_account_ID,
@account_currency_ID,
@company_ID
set @account_currency_ID2 =[dbo].[Currency2](@account_currency_ID);
set @company_ID2 =[dbo].[company1](@company_ID);
set @parent_account_ID2 =(select series from account where account_name= @parent_account_ID);
----------------------------------------------------------------------
declare @Series_1 varchar(50)
,@series2 varchar(50)
set @Series_1=( select top 1 series from Account order by ID desc)
IF(@Series_1 is not null)
begin
select @Series2=CONCAT('AC-',RIGHT(@Series_1, CHARINDEX('-', REVERSE(@Series_1)) - 1)+1 )
end
else
begin
SELECT @Series2=CONCAT('AC-','1')
end
begin try
begin tran
insert into
[dbo].[Account4](
Member
75 Points
513 Posts
how to insert multiple row from one table to another by cursor
Mar 20, 2019 07:56 PM|zhyanadil.it@gmail.com|LINK
i have this i want to insert multile row from table account3 into table account4 but only last row will be inserted how can i solve it
my cursor code here
declare @account_name varchar(50),@account_number varchar(50),@is_group varchar(50),@root_type varchar(50),
@report_type varchar(50),@account_type varchar(50),@tax_rate varchar(50),@freeze_account varchar(50),@balance_must_be varchar(50),
@parent_account_ID varchar(50),@account_currency_ID varchar(50),@company_ID varchar(50)
declare cur CURSOR LOCAL for select account_name,company_ID,account_number,is_group,root_type,report_type,account_type,tax_rate,freeze_account,
balance_must_be,parent_account_ID,account_currency_ID
from Account3
open cur
fetch next from cur into
@account_name,
@account_number ,
@is_group,
@root_type ,
@report_type,
@account_type,
@tax_rate,
@freeze_account,
@balance_must_be,
@parent_account_ID,
@account_currency_ID,
@company_ID
while @@FETCH_STATUS = 0 BEGIN
--execute your sproc on each row
exec [dbo].[INSERT_Account_empty]
@account_name=@account_name,
@account_number=@account_number,
@is_group=@is_group,
@root_type=@root_type,
@report_type=@report_type,
@account_type=@account_type,
@tax_rate=@tax_rate,
@freeze_account=@freeze_account,
@balance_must_be=@balance_must_be,
@parent_account_ID=@parent_account_ID,
@account_currency_ID=@account_currency_ID,
@company_ID=NULL
fetch next from cur into
@account_name,
@account_number ,
@is_group,
@root_type ,
@report_type,
@account_type,
@tax_rate,
@freeze_account,
@balance_must_be,
@parent_account_ID,
@account_currency_ID,
@company_ID
END
close cur
deallocate cur
my procedure
alter proc [dbo].[INSERT_Account_empty]
@series varchar(50)=null,
@account_name varchar(50)=null,
@company_ID varchar(50)=null,
@account_number varchar(50)=null,
@is_group varchar(50)=null,
@root_type varchar(50)=null,
@report_type varchar(50)=null,
@account_type varchar(50)=null,
@tax_rate varchar(50)=null,
@freeze_account varchar(50)=null,
@balance_must_be varchar(50)=null,
@parent_account_ID varchar(50)=null,
@account_currency_ID varchar(50)=null
as
begin
declare
@account_currency_ID2 varchar(50),
@company_ID2 varchar(50),
@parent_account_ID2 varchar(50);
set @account_currency_ID2 =[dbo].[Currency2](@account_currency_ID);
set @company_ID2 =[dbo].[company1](@company_ID);
set @parent_account_ID2 =(select series from account where account_name= @parent_account_ID);
----------------------------------------------------------------------
declare @Series_1 varchar(50)
,@series2 varchar(50)
set @Series_1=( select top 1 series from Account order by ID desc)
IF(@Series_1 is not null)
begin
select @Series2=CONCAT('AC-',RIGHT(@Series_1, CHARINDEX('-', REVERSE(@Series_1)) - 1)+1 )
end
else
begin
SELECT @Series2=CONCAT('AC-','1')
end
begin try
begin tran
insert into
[dbo].[Account4](
series,
account_name,
company_ID,
account_number,
is_group,
root_type,
report_type,
account_type,
tax_rate,
freeze_account,
balance_must_be,
parent_account_ID,
account_currency_ID
)
values
(@Series2,
@account_name,
@company_ID2,
@account_number,
@is_group,
@root_type,
@report_type,
@account_type,
@tax_rate,
@freeze_account,
@balance_must_be,
@parent_account_ID2,
@account_currency_ID2
)
commit tran
end try
begin catch
rollback tran
end catch
end