Cursor-SQL Help- Need another Column for Dynamic SQL

Question

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<>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<>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<>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!

Paul White · Accepted Answer · 2025-01-16 11:21:25Z

This query should give you the results you need without using a cursor. char(10) and char(13) are used for line feed & 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)

Perfect! Worked like a charm. Thank you Randi

DBA_Kyle
– DBA_Kyle

2019-03-14 13:59:53 +00:00
Commented Mar 14, 2019 at 13:59 — DBA_Kyle
– DBA_Kyle, Commented Mar 14, 2019 at 13:59

HandyD · Accepted Answer · 2019-03-13 23:49:53Z

You have a spelling mistake:

FETCH NEXT from secondcolumn into @seondcol

The variable is @seondcol but earlier it was declared as @secondcol. Also, you're opening both cursors then deallocating them BEFORE you're setting the @Command variable value:

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

Lastly, while cursors do have some use cases, this is probably not one of them as the return value can be retrieved with a simple query. Below code will bring back the identity column and second ordinal position column for all tables with an LU prefix in a single query:

SET NOCOUNT ON

DECLARE @Command VARCHAR(MAX),
    @NL VARCHAR(5) = CHAR(10)

SELECT @Command = 'SET IDENTITY_INSERT '+QUOTENAME(c1.TABLE_SCHEMA)+'.'+QUOTENAME(c1.TABLE_NAME) +' ON;' + @NL + 
'INSERT into '+QUOTENAME(c1.TABLE_SCHEMA)+'.'+QUOTENAME(c1.TABLE_NAME) +' ('+ QUOTENAME(c1.COLUMN_NAME)+', '+QUOTENAME(c2.COLUMN_NAME)+')' + @NL + 
'VALUES(-1, ''Unknown'');' + @NL + 
'SET IDENTITY_INSERT '+QUOTENAME(c1.TABLE_SCHEMA)+'.'+QUOTENAME(c1.TABLE_NAME) +' OFF;' + @NL + 
'GO'
FROM INFORMATION_SCHEMA.COLUMNS c1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c2 on object_id(c1.TABLE_NAME) = object_id(c2.TABLE_NAME) 
    AND SCHEMA_ID(c1.TABLE_SCHEMA) = SCHEMA_ID(c2.TABLE_SCHEMA)
WHERE COLUMNPROPERTY(object_id(c1.TABLE_NAME), c1.COLUMN_NAME, 'IsIdentity') = 1
    AND c2.ORDINAL_POSITION = 2
ORDER BY c1.TABLE_NAME;

SELECT @Command
PRINT @Command

This query only returns the last answer unfortunately. Thank you for taking the time to look into this though! Much appreciated — DBA_Kyle
– DBA_Kyle, Commented Mar 14, 2019 at 13:58

Stack Exchange Network

Cursor-SQL Help- Need another Column for Dynamic SQL

2 Answers 2

Your Answer

Hot Network Questions

Cursor-SQL Help- Need another Column for Dynamic SQL

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions