CODEWITHSUNDEEP
sqlsql-server-2012cursordynamic-sql
SSingh
SSingh

Reputation: 153

Loop through a resultset

I want to add 4 new columns to about a hundred tables in my database. To do this I'm using the following:

SET NOCOUNT ON
DECLARE @T NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)

DECLARE TABLE_CURSOR CURSOR FOR 
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%ABC_%'

 OPEN TABLE_CURSOR

 FETCH NEXT FROM TABLE_CURSOR 
 INTO @T
    WHILE @@FETCH_STATUS = 0
        BEGIN

            SET @SQL = N'ALTER TABLE ' + Quotename(@T) +
                'ADD COL1 NVARCHAR(50),
                    COL2 NVARCHAR(50),
                    COL3 NVARCHAR(10),
                    COL4 NVARCHAR(6)'
            EXEC (@SQL)

        END 
CLOSE TABLE_CURSOR  
DEALLOCATE TABLE_CURSOR

I've also tried using +@T + instead of Quotename but what happens is the columns are added to the first table in the result set but then it crashes out with an out of memory exception error.

Is there a better/efficient way to loop through a result set and add these columns? I could build a SSIS to do that but no guarantees it will work. I know cursors are generally slow but I thought for 100 tables it should be doable even if a little slow.

Upvotes: 1

Views: 184

Answers (2)

Markov
Markov

Reputation: 150

Here is the way I would do it.

DECLARE @SQL varchar(max);
SET @SQL = '';
SELECT @SQL = @SQL + 'ALTER TABLE '+ CAST(QUOTENAME(s.name) as Varchar(10))+'.'+Cast(QUOTENAME(t.name) as VARCHAR(50)) + ' ADD COL1 NVARCHAR(50), COL2 NVARCHAR(50),COL3 NVARCHAR(10), COL4 NVARCHAR(6); '
FROM sys.tables t
Join sys.all_columns ac on t.object_id = ac.object_id --Remove this join if you arent searching for tables with specific name
JOIN sys.schemas s on t.schema_id = t.schema_id
Where t.type = 'U' --tables 
      and s.schema_id = 1 --Your shema ID 
    --and ac.Name like '%Tables with this column%'

--Exec(@SQL)
PRINT @SQL

Upvotes: 0

Ryan Wilson
Ryan Wilson

Reputation: 10765

In your WHILE loop, you are missing the Fetch Next, without it, it creates an infinite loop, that is why you are getting an out of memory exception:

WHILE @@FETCH_STATUS = 0
        BEGIN

            SET @SQL = N'ALTER TABLE ' + Quotename(@T) +
                'ADD COL1 NVARCHAR(50),
                    COL2 NVARCHAR(50),
                    COL3 NVARCHAR(10),
                    COL4 NVARCHAR(6)'
            EXEC (@SQL)

            --Need this next line to move the cursor to the next record
            --Without this you create an infinite loop
            FETCH NEXT FROM TABLE_CURSOR INTO @T


        END

Upvotes: 1

Related Questions