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](
but i need by cursor because in the procedure my series is dynamic and primary
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
You use join to plug in the value and manipulate the value in your select before insert into your target table.
If you continue your cursor solution, I don't want to check it for you. Cursor has some usages (very limited for performance reason) and try to learn set based solutions for database queries.
According to your description, you don't need to use cursor, I suggest you to use merge into, you could use merge to synchronize between the two tables. The user can select some data of a table and compare it with another table. If it is found that there
is no such data, the data row will be inserted into the target table.
Here is the demo , I hope it could help you.
First we could create two tables
/****** CREATE Sales1 ONE TABLE ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sales1](
[PersonID] [float] NULL,
[LastName] [nvarchar](255) NULL,
[FirstName] [nvarchar](255) NULL,
[Address] [nvarchar](255) NULL,
[Amount] [float] NULL,
[Payment_Mode] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** CREATE Sales2 ONE TABLE ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ('sales2','U') IS NOT NULL
DROP TABLE sales2;
GO
CREATE TABLE [dbo].[Sales2](
[PersonID] [float] NULL,
[LastName] [nvarchar](255) NULL,
[FirstName] [nvarchar](255) NULL,
[Address] [nvarchar](255) NULL,
[Amount] [float] NULL,
[Payment_Mode] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Sales1]
([PersonID]
,[LastName]
,[FirstName]
,[Address]
,[Amount]
,[Payment_Mode])
Values
('11','Sales1LN1','Sales1FN2','Sales1ADD2','11','CASH'),
('12','Sales1LN2','Sales1FN2','Sales1ADD2','12','CASH'),
('13','Sales1LN2','Sales1FN2','Sales1ADD2','13','CASH'),
('14','Sales1LN2','Sales1FN2','Sales1ADD2','14','CASH'),
('15','Sales1LN2','Sales1FN2','Sales1ADD2','15','CASH')
INSERT INTO [dbo].[Sales2]
([PersonID]
,[LastName]
,[FirstName]
,[Address]
,[Amount]
,[Payment_Mode])
Values
('1','Sales2LN1','Sales2FN2','Sales2ADD2','11','CASH'),
('2','Sales2LN1','Sales2FN2','Sales2ADD2','12','CASH'),
('3','Sales2LN2','Sales2FN2','Sales2ADD2','13','CASH'),
('4','Sales2LN2','Sales2FN2','Sales2ADD2','14','CASH'),
('5','Sales2LN2','Sales2FN2','Sales2ADD2','15','CASH'),
('11','Sales2LN2','Sales2FN2','Sales2ADD2','11','CASH')
We could see:
Then we could use merge into as below:
Merge into sales1 as tab1
using(select * from Sales2) as tab2
on tab1.PersonID=tab2.PersonID
when matched then
update set
tab1.lastname=tab2.lastname,
tab1.FirstName=tab2.FirstName,
tab1.Address=tab2.Address,
tab1.Amount=tab2.Amount,
tab1.Payment_Mode=tab2.Payment_Mode
when not matched then
insert values(tab2.PersonID,tab2.LastName,tab2.FirstName,tab2.Address,tab2.Amount,tab2.payment_mode);
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
All-Star
123252 Points
10024 Posts
Moderator
Re: how to insert multiple row from one table to another by cursor
Mar 20, 2019 08:28 PM|limno|LINK
You don't need a cursor at all.
A simple insert into
select... from ... should do.
You can a case expression to modify some column as you needed.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
75 Points
513 Posts
Re: how to insert multiple row from one table to another by cursor
Mar 20, 2019 08:39 PM|zhyanadil.it@gmail.com|LINK
but i need by cursor because in the procedure my series is dynamic and primary
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
All-Star
123252 Points
10024 Posts
Moderator
Re: how to insert multiple row from one table to another by cursor
Mar 20, 2019 09:00 PM|limno|LINK
You use join to plug in the value and manipulate the value in your select before insert into your target table.
If you continue your cursor solution, I don't want to check it for you. Cursor has some usages (very limited for performance reason) and try to learn set based solutions for database queries.
Good luck.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
75 Points
513 Posts
Re: how to insert multiple row from one table to another by cursor
Mar 21, 2019 04:13 AM|zhyanadil.it@gmail.com|LINK
how can i use join for it?
Participant
1300 Points
522 Posts
Re: how to insert multiple row from one table to another by cursor
Mar 21, 2019 06:01 AM|Wei Zhang|LINK
Hi zhyanadil.it@gmail.com
According to your description, you don't need to use cursor, I suggest you to use merge into, you could use merge to synchronize between the two tables. The user can select some data of a table and compare it with another table. If it is found that there is no such data, the data row will be inserted into the target table.
Here is the demo , I hope it could help you.
First we could create two tables
We could see:

Then we could use merge into as below:
We could see in sales1:

Best Regards
Wei