Cursor-SQL Help- Need another Column for Dynamic SQL

1 vote
2 answers
361 views
                          My company decided to change the value "Unknown" to (-1) in ALL of our lookup tables (tables that end with 'LU'). I am trying to write a script to automatically add a value for VALUES(-1, 'Unknown')

    BEGIN TRAN
    
    DECLARE @tablename nvarchar(250)
    DECLARE @idcolumn nvarchar(250)
    DECLARE @command nvarchar(500)
    
    DECLARE LU CURSOR LOCAL FAST_FORWARD for
    select TABLE_NAME, COLUMN_NAME
          from INFORMATION_SCHEMA.COLUMNS
           where TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE '%LU'
           and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
    	   order by TABLE_NAME
    
    OPEN LU
    WHILE 1=1
    BEGIN
    	FETCH NEXT FROM LU INTO @tablename, @idcolumn
    	IF @@FETCH_STATUS0
    	BEGIN 
    		BREAK
    END
    
    
    
    SET @command = N'
    SET IDENTITY_INSERT '+ @tablename+ ' ON
    INSERT into '+ @tablename+ ' ('+@idcolumn+')
    VALUES(-1)
    SET IDENTITY_INSERT ' +@tablename+ ' Off'
    PRINT @command
    END 
    CLOSE LU
    DEALLOCATE LU
    
    ROLLBACK tran

which outputs something like this:

    SET IDENTITY_INSERT AccessiblityLU ON
    INSERT into AccessiblityLU (AccessiblityID)
    VALUES(-1)
    SET IDENTITY_INSERT AccessiblityLU Off

I am a beginner and realize Cursors shouldn't be used when there are other options available. I tried adding another cursor to grab the second column from each table (the ordinal position of the columns i need is always 2 luckily).
I tried this with no luck:

    BEGIN TRAN
    --DECLARE VARIABLES
    DECLARE @tablename nvarchar(250)
    DECLARE @idcolumn nvarchar(250)
    DECLARE @command nvarchar(500)
    DECLARE @secondcol nvarchar(100)
    
    --FIRST CURSOR
    DECLARE LU CURSOR LOCAL FAST_FORWARD for
    select TABLE_NAME, COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE '%LU'
    and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
    order by TABLE_NAME
    
    OPEN LU
    while 1=1
    BEGIN
    FETCH NEXT from LU into @tablename, @idcolumn
    if @@fetch_status0
    begin 
    	break
    end
    DECLARE @secondcol nvarchar(100)
    --SECOND CURSOR
    DECLARE secondcolumn cursor local fast_forward for 
    select name    
    from sys.columns
    where OBJECT_NAME(object_id) = @tablename AND column_id=2
    
    OPEN secondcolumn
    while 1=1
    BEGIN
    FETCH NEXT from secondcolumn into @seondcol
    if @@fetch_status0
    begin 
    	break
    end
     
    deallocate LU
    deallocate secondcolumn
    
    
    
    SET @command = N'
    SET IDENTITY_INSERT '+ @tablename+ ' ON
    INSERT into '+ @tablename+ ' ('+@idcolumn+', '+@secondcol+')
    VALUES(-1, ''Unknown'')
    SET IDENTITY_INSERT ' +@tablename+ ' Off'
    PRINT @command
    end

  Any help would be greatly Appreciated!
                        
Asked by DBA_Kyle (53 rep)
Mar 13, 2019, 09:34 PM
Last activity: Jan 16, 2025, 11:21 AM
Cursor-SQL Help- Need another Column for Dynamic SQL

Related Questions
