I'm trying to execute the same sequence of actions on a list of tables, which are selected using a where clause.
Select table_name
From INFORMATION_SCHEMA.COLUMNS
Where column_name = 'fieldA';
This generates a list of table names - these are the tables I want to handle with a for loop. Is there a way to iterate through this list of tables, something like this?
for i in @tablelist:
alter table i add new_col varchar(8);
update i set new_col = old_col;
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
You do not need to use loops for this. You can use the system view to generate your dynamic sql for you a lot simpler (and faster) than loops.
declare @SQL nvarchar(max) = ''
select @SQL = @SQL + 'alter table ['+ c.TABLE_NAME +'] add new_col varchar(8);
update ['+ c.TABLE_NAME + '] set new_col = old_col;'
from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'fieldA'
select @SQL
--uncomment below when you are satisfied with the dynamic sql
--exec sp_executesql @SQL
The data types nvarchar(max) and varchar are incompatible in the subtract operator. The old_col and fieldA fields are always varchar(10) in each table. Tried changing declare @SQL varchar(max) = '' but same error.For looping you either have to use cursors or while exists construct, where you remove entry from temporary table each time you process it.
As for passing table name to query, there is no way to do that without creating dynamic SQL query. You do that by creating query in string with concatenating table name into the right place. Use QUOTENAME function to perform quoting of name if there are any spaces or weird characters.
Example with cursor:
DECLARE @tables TABLE (
Name SYSNAME
);
DECLARE @tableName SYSNAME;
DECLARE @sql NVARCHAR(4000);
INSERT INTO @tables
SELECT i.table_name
FROM INFORMATION_SCHEMA.COLUMNS i
WHERE i.column_name = 'fieldA';
DECLARE cur CURSOR LOCAL FAST_FORWARD
FOR
SELECT Name
FROM @tables;
OPEN cur;
FETCH NEXT FROM cur
INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'ALTER TABLE ' + QUOTENAME(@tableName) + N' ADD new_col varchar(8);';
SET @sql = @sql + N'UPDATE ' + QUOTENAME(@tableName) + N' SET new_col = old_col;';
EXEC sp_executesql @sql;
FETCH NEXT FROM cur
INTO @tableName;
END
CLOSE cur;
DEALLOCATE cur;
One way to accomplish this action would be to feed your list of tables you want to alter into a SQL cursor and iterate through the cursor to execute the ALTER table command on each table. The documentation on how to create a SQL cursor can be found here:
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql
Another way to accomplish this would be using a while loop in SQL with a count iterator. Information on this solution can be found here:
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql
