Can I loop through a table variable in T-SQL?
Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT.
Try this:
DECLARE @RowsToProcess int
DECLARE @CurrentRow int
DECLARE @SelectCol1 int
DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @RowsToProcess=@@ROWCOUNT
SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@SelectCol1=col1
FROM @table1
WHERE RowID=@CurrentRow
--do your thing here--
END
Is there a way to loop through a table variable in TSQL without using a cursor?
First of all you should be absolutely sure you need to iterate through each row — set based operations will perform faster in every case I can think of and will normally use simpler code.
Depending on your data it may be possible to loop using just SELECT
statements as shown below:
Declare @Id int
While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
Select Top 1 @Id = Id From ATable Where Processed = 0
--Do some processing here
Update ATable Set Processed = 1 Where Id = @Id
End
Another alternative is to use a temporary table:
Select *
Into #Temp
From ATable
Declare @Id int
While (Select Count(*) From #Temp) > 0
Begin
Select Top 1 @Id = Id From #Temp
--Do some processing here
Delete #Temp Where Id = @Id
End
The option you should choose really depends on the structure and volume of your data.
Note: If you are using SQL Server you would be better served using:
WHILE EXISTS(SELECT * FROM #Temp)
Using COUNT
will have to touch every single row in the table, the EXISTS
only needs to touch the first one (see Josef's answer below).
Using cursor to loop through a table variable in SQL Server
This uses two parts - first convert your string into a table, then do bulk inserts into your destination. No need for cursors.
** Please excuse any syntax errors as I'm doing it without access to your actual tables or functions so cant test it, but you get the idea
declare @in varchar(max)
set @in= '1/1/2018-2/1/2018,2/1/2018-3/1/2018,3/1/2018-4/1/2018,4/1/2018-5/1/2018,5/1/2018-6/1/2018,6/1/2018-7/1/2018,7/1/2018-8/1/2018,8/1/2018-9/1/2018,9/1/2018-10/1/2018,10/1/2018-11/1/2018,11/1/2018-12/1/2018,12/1/2018-12/31/2018'
declare @xml xml;
set @xml= convert(xml,'<r><f>'+replace(replace(@in,',','</t></r><r><f>'),'-','</f><t>') +'</t></r>')
declare @t table(id int identity, f date, t date)
insert @t
select
Tbl.Col.value('f[1]', 'date') f,
Tbl.Col.value('t[1]', 'date') t
FROM @xml.nodes('//r') Tbl(Col)
select * from @t
declare @count int;
select @count=count(*) from @t
INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
select id, 'SL Payroll',(select dbo.GetAverageCycleBetweenBids(f,t,'SL')) from @t
INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
select id+@count,'GV Payroll',(select dbo.GetAverageCycleBetweenBids(f,t,'GV')) from @t
INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
select id+@count*2,'Global Payroll',(select dbo.GetAverageCycleBetweenBids(f,t,'GVS')) from @t
INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
select id+@count*3,'TimeHCM',(select dbo.GetAverageCycleBetweenBids(f,t,'Time')) from @t
Loop through table by row T-SQL
I suggest to rewrite it using Cursors as follows faster:
DECLARE @IMAX INT,
@ICOUNT INT,
@INTERFACE_ID_36 INT,
@INTERFACE_ID_38 INT
DECLARE db_cursor CURSOR FOR
SELECT Interface_ID FROM INTERFACE_36_DATA
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @INTERFACE_ID_36
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @INTERFACE_ID_36
-- All your other selects
FETCH NEXT FROM db_cursor INTO @INTERFACE_ID_36
END
CLOSE db_cursor
DEALLOCATE db_cursor
Loop through table while records keep getting added to the table
I'd use a do while loop instead.
Have a variable called moreToProcess data type boolean.
Set the value before entering the loop using execute SQL statement based on count > 0
First step in the loop is to get a record to process using exec SQL.
Before exiting loop rerun the SQL to reset the variable.
Set to loop while moreToProcess is true.
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
Related Topics
How to Correct the Correlation Names on This SQL Join
No Unique or Exclusion Constraint Matching the on Conflict
How to Use a Package Constant in SQL Select Statement
Sql-Server Performance: What Is Faster, a Stored Procedure or a View
Execute Table-Valued Function on Multiple Rows
SQL Datediff - Find Datediff Between Rows
Is a Primary Key Necessary in SQL Server
Add a Row Number to Result Set of a SQL Query
Add Unique Constraint in SQL Server 2008 Gui
Indexes and Multi Column Primary Keys
How to Generate Scripts for All Triggers in Database Using Microsoft SQL Server Management Studio
Does the Order of Fields in a Where Clause Affect Performance in MySQL
Sqlite3 Unique Constraint Failed Error
Sorting on the Server or on the Client
Connect by Clause in Regex_Substr
SQL Query for Parent Child Relationship
Swap Unique Indexed Column Values in Database
How to Identify All Stored Procedures Referring a Particular Table