Bulk insert instead of cursor

Question

We have this structure:

Deposit 
PrimaryKey depositId
Depositofficerid 


1-> M Depositworker 
ForeignKey depositId
WorkerId
WorkerRoleGroupId = 1

For every Depositofficerid in Deposit table, there should be a record in the Depositworker table with workerid = Depositofficerid.

This is how I did it using cursor. I am wondering if there is a way to do a bulk insert into Depositworker table.

IF OBJECT_ID('tempdb..#tempdeposits') IS NOT NULL
    DROP TABLE dbo.#tempdeposits;

CREATE TABLE #tempdeposits (
    DepositId int
)
INSERT INTO #tempdeposits (DepositId)
    SELECT
        DepositId
    FROM Deposit
    WHERE
        CreatedDate = '2005-01-14 16:05:51.920'


DECLARE @DepositId int
DECLARE @getloans CURSOR
SET @getloans = CURSOR FOR
SELECT
    DepositId
FROM #tempdeposits
OPEN @getdeposits
FETCH NEXT FROM @getdeposits INTO @DepositId
WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (SELECT
            Depositworkerid
        FROM Depositworker
        WHERE DepositId = @DepositId
        AND WorkerRoleGroupId = 1)
    BEGIN
        PRINT @DepositId

        INSERT INTO DepositWorker (DepositId, WorkerId, WorkerRoleGroupId, CreatedBy, CreatedDate, UpdateCounter)
            SELECT
                @DepositId,
                Depositofficerid,
                1,
                1,
                GETDATE(),
                1
            FROM Deposit
            WHERE
                DepositId = @DepositId
    END
    FETCH NEXT
    FROM @getdeposits INTO @DepositId
END
CLOSE @getdeposits
DEALLOCATE @getdeposits

Can somehow help me with making this a bulk insert instead of cursor?

Answer 1

Yes, you can do this in a set-based fashion using a correlated NOT EXISTS:

INSERT INTO DepositWorker (DepositId, WorkerId, WorkerRoleGroupId, CreatedBy, CreatedDate, UpdateCounter)
SELECT  
    d.DepositId,
    d.Depositofficerid,
    1,
    1,
    GETDATE(),
    1
FROM Deposit d
WHERE 
    CreatedDate = '2005-01-14 16:05:51.920'
    AND NOT EXISTS(
        SELECT 1
        FROM DepositWorker dw
        WHERE
            dw.DepositId = d.DepositId
            AND dw.WorkerRoleGroupId = 1
    )