Cursor-SQL Help- Need another Column for Dynamic SQL 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_STATUS&lt;&gt;0
    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_status&lt;&gt;0
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_status&lt;&gt;0
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!
It appears that you are using SQL Server, SQL Server uses T-SQL while Oracle uses PL/SQL.

This query should give you the results you need without using a cursor. char(10) and char(13) are used for line feed &amp; carriage return, QUOTENAME() to get the square brackets around your objects.

SET NOCOUNT ON;

SELECT 'SET IDENTITY_INSERT '+QUOTENAME(I1.TABLE_SCHEMA)+'.'+QUOTENAME(I1.TABLE_NAME) +' ON;' + CHAR(10)+CHAR(13)
        +'INSERT into '+QUOTENAME(I1.TABLE_SCHEMA)+'.'+QUOTENAME(I1.TABLE_NAME) +' ('+ QUOTENAME(I1.COLUMN_NAME)+', '+QUOTENAME(I2.COLUMN_NAME)+')' + CHAR(10)+CHAR(13)
        +'VALUES(-1, ''Unknown'');' + CHAR(10)+CHAR(13)
        +'SET IDENTITY_INSERT '+QUOTENAME(I1.TABLE_SCHEMA)+'.'+QUOTENAME(I1.TABLE_NAME) +' OFF;' + CHAR(10)+CHAR(13)
        +'GO'
FROM INFORMATION_SCHEMA.COLUMNS I1
LEFT JOIN INFORMATION_SCHEMA.COLUMNS I2 
on object_id(I1.TABLE_NAME) = object_id(I2.TABLE_NAME) 
AND SCHEMA_ID(I1.TABLE_SCHEMA) = SCHEMA_ID(I2.TABLE_SCHEMA) AND I2.ORDINAL_POSITION = 2
WHERE I1.TABLE_SCHEMA = 'dbo' AND I1.TABLE_NAME LIKE '%LU' 
AND COLUMNPROPERTY(object_id(I1.TABLE_NAME), I1.COLUMN_NAME, 'IsIdentity') = 1 
ORDER BY I1.TABLE_NAME;


In SSMS, you could select Results to text:



And then execute the script, giving you the formatted results:



Test tables created

CREATE TABLE [dbo].[PREFIXLU] ([id] int identity(1,1), [val] varchar(255));
CREATE TABLE [dbo].[LUSUFFIX] ([id] int identity(1,1), [val] varchar(255));
CREATE TABLE [dbo].[LU] ([id] int identity(1,1), [val] varchar(255));


From these three tables, two tables, PREFIXLU and LU should be returned by the script.

I would really check and make sure that  ORDINAL_POSITION = 2 is 100% true for all the tables with LU as a suffix.

Result:

SET IDENTITY_INSERT [dbo].[LU] ON;

INSERT into [dbo].[LU] ([id], [val])

VALUES(-1, 'Unknown');

SET IDENTITY_INSERT [dbo].[LU] OFF;

GO
SET IDENTITY_INSERT [dbo].[PREFIXLU] ON;

INSERT into [dbo].[PREFIXLU] ([id], [val])

VALUES(-1, 'Unknown');

SET IDENTITY_INSERT [dbo].[PREFIXLU] OFF;

GO



  (1 row affected)
  
  (1 row affected)