for each in MS SQL SERVER?

Question

I have two tables:

Employees(EmployeeID, EmployeeName, EmployeeStatus, BasicSalary) and

EmployeePayroll (PayrollID, EmployeeID, VoucherNo, BasicSalary, SalaryMonth)

I want to make a for each loop for every employee in the first table so that I can insert dummy data like (0, EmployeeID,0 ,0 ,0) into the second table.

I tried to make it with a for loop but I could not make it, so is there a for each loop in MS SQL Server??

Answer 1

If you really require a loop, you can use a cursor. They are horribly inefficient, so you should avoid unless you absolutely require it:

DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT EmployeeID
    FROM Employees

DECLARE @id Int

-- Open the cursor
OPEN c

FETCH NEXT FROM c INTO @id
WHILE (@@FETCH_STATUS = 0)
BEGIN
    INSERT INTO EmployeePayroll
    SELECT 0, @id, 0, 0, 0

    -- do other stuff

    FETCH NEXT FROM c INTO @id
END

-- Close and deallocate the cursor
CLOSE c
DEALLOCATE c

Answer 2

Use the following statement:

INSERT INTO EmployeePayroll
SELECT
  0,EmployeeID ,0,0,0
FROM
  Employees

You can check for the existance of the record before inserting it by appending:

WHERE
  ID NOT IN
  (
     SELECT
       EmployeeID
     FROM
       EmployeePayroll
  )