how to insert multiple row from one table to another by cursor

Question

User-1634604574 posted

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

Answer 1

User77042963 posted

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.

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

)

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

Answer 2

User-1634604574 posted

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

Answer 3

User77042963 posted

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.

Answer 4

User-1634604574 posted

how can i use join for it?

Answer 5

User-1174608757 posted

Hi [e-mail]

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); 

We could see in sales1:

Best Regards

Wei