对于 MS SQL SERVER 中的每个？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5856073/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
for each in MS SQL SERVER?
提问by HAJJAJ
I have two tables:
我有两个表：
Employees(EmployeeID, EmployeeName, EmployeeStatus, BasicSalary)and
Employees(EmployeeID, EmployeeName, EmployeeStatus, BasicSalary)和
EmployeePayroll (PayrollID, EmployeeID, VoucherNo, BasicSalary, SalaryMonth)
EmployeePayroll (PayrollID, EmployeeID, VoucherNo, BasicSalary, SalaryMonth)
I want to make a for eachloop for every employee in the first table so that I can insert dummy data like (0, EmployeeID,0 ,0 ,0)into the second table.
我想为for each第一个表中的每个员工创建一个循环，以便我可以将虚拟数据插入(0, EmployeeID,0 ,0 ,0)到第二个表中。
I tried to make it with a forloop but I could not make it, so is there a for eachloop in MS SQL Server??
我试图用一个for循环来制作它，但我无法做到，那么for eachMS SQL Server 中是否有一个循环？？
回答by David Duffett
If you reallyrequire 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
回答by Akram Shahda
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
)
回答by Manikandan Sethuraju
This is the one way to achieve your requirement using While loop & Temp table variable,
这是使用 While 循环和临时表变量实现您的要求的一种方法，
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.
注意：假设员工记录已经存在，我们可以在这个 while 循环中更新 EmployeePayroll 记录。
回答by Thangamani Palanisamy
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
这是实现这一目标的最优化方法
回答by Bishoy Hanna
I was looking for a decent way for a foreach loop in SQL and came up with this easy code
我正在为 SQL 中的 foreach 循环寻找一种体面的方法，并想出了这个简单的代码
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
回答by Sarathi B
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
试试Cross Apply。这是游标和循环的最佳选择。如果您将交叉应用的执行计划与其他方法进行比较，您会发现它明显更快
回答by Hyman.mike.info
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
使用需要为零的列的默认约束
payrollID、VoucherNo、BasicSalary、Salary-Month
插入 EmployeeID 并使用上述概念
