locked
how to insert multiple row from one table to another by cursor RRS feed

  • 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

    Wednesday, March 20, 2019 7:56 PM

All replies

  • 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

    Wednesday, March 20, 2019 8:28 PM
  • 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

    Wednesday, March 20, 2019 8:39 PM
  • 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.

    Wednesday, March 20, 2019 9:00 PM
  • User-1634604574 posted

    how can i use join for it?

    Thursday, March 21, 2019 4:13 AM
  • User-1174608757 posted

    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

    /****** 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

    Thursday, March 21, 2019 6:01 AM