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
  )

Answer 3

This is the one way to achieve your requirement using While loop & Temp table variable,

The sample query is given below,

--: Get the Employees table data & insert this to temp table variable
Declare @TempEmpTbl Table (RowId int identity, EmployeeID int, EmployeeName nvarchar(100), EmployeeStatus int, BasicSalary int);
Insert into @TempEmpTbl Select * from Employees;

--: temp variables
Declare @TempEmpCount Int = (Select Count(RowId) From @TempEmpTbl);
Declare @MinCount Int = 1;
Declare @GetEmpId int;

--: while loop for EmployeePayroll tbl insertion based on Employees data
While(@TempEmpCount >= @MinCount)
Begin
    Set @GetEmpId = (Select EmployeeID From @TempEmpTbl Where RowId = @MinCount);   
    Insert into EmployeePayroll values (0, @GetEmpId,0 ,0 ,0)
    Set @MinCount = @MinCount + 1;
End

Note : Suppose the employee record is already there, we can able to update EmployeePayroll records from within this while loop.

Answer 4

INSERT INTO EmployeePayroll
SELECT
 0,E.EmployeeID ,0,0,0
FROM
Employees E
WHERE 
NOT EXISTS
(
  SELECT
       1
     FROM
       EmployeePayroll WHERE EmployeeID = E.Id
)

Hope this helps

This is most optimized way to achieve this

Answer 5

I was looking for a decent way for a foreach loop in SQL and came up with this easy code

SELECT RowNum = ROW_NUMBER() OVER(ORDER BY Id),*
INTO #Locations
FROM [dbo].[Location]

DECLARE @MaxRownum INT
SET @MaxRownum = (SELECT MAX(RowNum) FROM #Locations)

DECLARE @Iter INT
SET @Iter = (SELECT MIN(RowNum) FROM #Locations)



WHILE @Iter <= @MaxRownum
BEGIN
DECLARE @currentCountry varchar(250)
DECLARE @locId int
SET @currentCountry = ( SELECT Country 
                        FROM #Locations
                        WHERE RowNum = @Iter)

SET @LocId = (  SELECT Id 
                FROM #Locations
                WHERE RowNum = @Iter)



    -- run your operation here
Update [dbo].[Location]
SET CountryId  = (SELECT (Id) FROM [dbo].[Site] WHERE [dbo].[Site].Name = @currentCountry)
WHERE [dbo].[Location].Id = @locId


    SET @Iter = @Iter + 1
END

DROP TABLE #Locations

Answer 6

Try Cross Apply. This is the best alternate for cursor and looping. Mover over if you compare the execution plan of cross apply with other methods you will notice its noticeably faster

http://sqlserverplanet.com/sql-2005/cross-apply-explained

Answer 7

HI

USE Default constraints for the column which needs to be zero

payrollID,VoucherNo, BasicSalary, Salary-Month

insert the EmployeeID for the same and use the above mentioned concept