user575219 user575219 - 6 months ago 13
SQL Question

Bulk insert instead of cursor

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

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
    )