For Each in Ms SQL Server

SQL Server FOR EACH Loop

SQL is primarily a set-orientated language - it's generally a bad idea to use a loop in it.

In this case, a similar result could be achieved using a recursive CTE:

with cte as
(select 1 i union all
 select i+1 i from cte where i < 5)
select dateadd(d, i-1, '2010-01-01') from cte

How to write a foreach in SQL Server?

You seem to want to use a CURSOR. Though most of the times it's best to use a set based solution, there are some times where a CURSOR is the best solution. Without knowing more about your real problem, we can't help you more than that:

DECLARE @PractitionerId int

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT DISTINCT PractitionerId 
FROM Practitioner

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
WHILE @@FETCH_STATUS = 0
BEGIN 
    --Do something with Id here
    PRINT @PractitionerId
    FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

for each in MS SQL SERVER?

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
  )

What is the best way to do a Foreach Update in T-SQL?

You can also use Something like this.

SQL Fiddle

Setup and Data

CREATE TABLE TestTable
(
    Id INT IDENTITY(1,1),
    firstname VARCHAR(20) DEFAULT(''),
    lastname VARCHAR(20) DEFAULT('')
);

CREATE TABLE randomnames
(
    Id INT IDENTITY(1,1),
    firstname VARCHAR(20),
    lastname  VARCHAR(20)
);

 insert into TestTable DEFAULT VALUES;
 insert into TestTable DEFAULT VALUES;
 insert into TestTable DEFAULT VALUES;
 insert into TestTable DEFAULT VALUES;
 insert into TestTable DEFAULT VALUES;
 insert into randomnames VALUES('F1','L1'),('F2','L2'),('F3','L3'),('F4','L4');

Query

;WITH CTE as
(
    SELECT *,ABS(Checksum(NewID()) % 4) + 1 as fnameid,ABS(Checksum(NewID()) % 4) + 1 as lnameid
    FROM TestTable
)
update CTE
set 
FirstName = (select firstname from randomnames where ID =fnameid),
LastName = (select lastname from randomnames where ID =lnameid);

Output

Id  firstname   lastname
1   F4  L1
2   F3  L3
3   F4  L2
4   F3  L2
5   F2  L1

Iterate through a list of strings In SQL Server?

What you created is not a list but a table variable. So how to Iterate over a table. Below is a simple example and I think you can proceed after if you understand it:

(Note: Cursors are not efficient when it comes to performance and large tables)

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')

DECLARE @value VARCHAR(50)

DECLARE db_cursor CURSOR FOR  
SELECT Value FROM @MyList
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @value   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       PRINT @value

       -- PUT YOUR LOGIC HERE
       -- MAKE USE OR VARIABLE @value wich is Data1, Data2, etc...

       FETCH NEXT FROM db_cursor INTO @value   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Prints:

Data1
Data2
Data3
Data4
Data5
Data6
Data7
Data8

So you have inside @value variable Data, Data2 .. etc . I think this solves your problem.

Alternative way is using a WHILE loop + OFFSET + FETCH NEXT :

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')

DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)
DECLARE @VALUE VARCHAR(50);

WHILE @COUNTER < @MAX
BEGIN

SET @VALUE = (SELECT VALUE FROM
      (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , Value from @MyList) R 
       ORDER BY R.[index] OFFSET @COUNTER 
       ROWS FETCH NEXT 1 ROWS ONLY);

PRINT @VALUE

SET @COUNTER = @COUNTER + 1

END

You get the same result

t-sql for loop to iterate through tables

You could try this:

DECLARE @Table TABLE
(
TableName VARCHAR(50),
Id int identity(1,1)
)

INSERT INTO @Table
Select table_name From INFORMATION_SCHEMA.COLUMNS 
Where column_name = 'fieldA'

DECLARE @max int
DECLARE @SQL VARCHAR(MAX) 
DECLARE @TableName VARCHAR(50)
DECLARE @id int = 1

select @max = MAX(Id) from @Table

WHILE (@id <= @max)
BEGIN

SELECT @TableName = TableName FROM @Table WHERE Id = @id
SET @SQL =     'alter table '+ @TableName +' add new_col varchar(8);
                update '+ @TableName + ' set new_col = old_col;'
PRINT(@SQL)  --COMMENT THIS LINE OUT AND COMMENT IN THE NEXT EXEC(@SQL) IF YOU SEE THE CORRECT OUTPUT
--EXEC(@SQL)
SET @id = @id +1
END

How to create a select for each row of table in ms sql server?

Added the JOIN lines to join your 3 tables:

SELECT [PT].[CODE], [TL].[LINE NUM], [TD].[DOCUMENT ID]
FROM [TABLE DOCUMENT] as TD
JOIN [TABLE LINES]    as TL
    on TL.[DOCUMENT ID] = TD.[DOCUMENT ID]
JOIN [PRODUCTS TABLE] as PT
    on PT.[PRODUCTS ID] = TL.[PRODUCT ID]
WHERE TYPE='KYT' AND PT.ISACTIVE = 1

---

Related Topics