CODEWITHSUNDEEP
sqlsql-server
Mohamed abdelwahed
Mohamed abdelwahed

Reputation: 15

Insert does not work with cursor

I have a problem that my Insert statement doesn't work. I created temp tables, then the insert statement is working based on two columns in this temp tables.

The data exists in the temp tables, but the insert is not working with no error appearing. Thanks in advance 

DECLARE JPCUSTOMER_CURSOR CURSOR FOR 
    SELECT
        cu.CustomerNo, jp.ID 
    FROM
        CUSTOMERNO# cu, SalesmanNo# sa, JourneyPlan JP
    WHERE
        cu.OCCURRENCE = sa.OCCURRENCE
        AND jp.AssignedTO = sa.SalesmanNo

OPEN JPCUSTOMER_CURSOR

FETCH NEXT FROM JPCUSTOMER_CURSOR INTO @CUST_ID,@JP_ID

WHILE (@@FETCH_STATUS = 0)
BEGIN  
    INSERT INTO [JPCustomer] ([CustomerID ], [JPID], [Frequency], [StartWeek], 
                              [sat], [sun], [mon], [tue], [wed], [thu], [fri],
                              [VisitOrder], [ModifiedOn], [ModifiedBy],
                              [CreatedOn], [Createdby],
                              [RecordSource], [IsPotential])
    VALUES (@CUST_ID, @JP_ID, 4, 1, 
            1, 1, 1, 1, 1, 1, 1, 
            NULL, NULL, NULL,  
            NULL, NULL, 0, 0)
END   

CLOSE JPCUSTOMER_CURSOR;  
DEALLOCATE JPCUSTOMER_CURSOR;

Upvotes: 1

Views: 310

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

You don't need and shouldn't use a cursor for this. Just an INSERT ... SELECT. EG

with q as
(
    select cu.CustomerID CUST_ID, jp.ID JPID
    from CUSTOMERNO# cu
    join SalesmanNo# sa
      on cu.OCCURRENCE = sa.OCCURRENCE
    join JourneyPlan JP
      on jp.AssignedTO = sa.SalesmanNo

)
INSERT INTO [JPCustomer] ([CustomerID],
                           [JPID],
                           [Frequency],
                           [StartWeek],
                           [sat],
                           [sun],
                           [mon],
                           [tue],
                           [wed],
                           [thu],
                           [fri],
                           [VisitOrder],
                           [ModifiedOn],
                           [ModifiedBy],
                           [CreatedOn],
                           [Createdby],
                           [RecordSource],
                           [IsPotential])
SELECT  CustomerID,
        JPID,
        4,
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        0,
        0
from q;

Upvotes: 1

juergen d
juergen d

Reputation: 204894

You don't need a cursor for what you are doing. This will be much faster:

INSERT INTO [JPCustomer] ([CustomerID ],
                           [JPID],
                           [Frequency],
                           [StartWeek],
                           [sat],
                           [sun],
                           [mon],
                           [tue],
                           [wed],
                           [thu],
                           [fri],
                           [VisitOrder],
                           [ModifiedOn],
                           [ModifiedBy],
                           [CreatedOn],
                           [Createdby],
                           [RecordSource],
                           [IsPotential])
select cu.CustomerNo,
       jp.ID,
        4,
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        0,
        0 
from CUSTOMERNO# cu
join SalesmanNo# sa on cu.OCCURRENCE = sa.OCCURRENCE
join JourneyPlan JP on jp.AssignedTO = sa.SalesmanNo

Upvotes: 2

Related Questions