sql-generate-insert icon indicating copy to clipboard operation
sql-generate-insert copied to clipboard

@PopulateIdentityColumn = 1 not working with tables without identity columns

Open jjmontes opened this issue 2 years ago • 0 comments

Thanks!

First of all, the project is simple and extremely useful; thanks for that!!!

Do you have a cafecito app, patreon or similar account?

Describe the bug

When I use the parameter @PopulateIdentityColumn = 1, if the table does not have an identity column the SET IDENTITY_INSERT @tableName ON and SET IDENTITY_INSERT @tableName OFF statements are still generated

To Reproduce

Steps to reproduce the behavior:

  1. Create a table without Identity column, for example:
CREATE TABLE [dbo].[Administrador](
	[Id] [bigint] NOT NULL,
 CONSTRAINT [PK_dbo.Administrador] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

  1. Execute EXEC GenerateInsert 'Administrador', @PopulateIdentityColumn = 1, output is
--INSERTs generated by GenerateInsert (Build 6)
--Project page: http://github.com/drumsta/sql-generate-insert
SET NOCOUNT ON
SET IDENTITY_INSERT Administrador ON
INSERT INTO Administrador
([Id])
VALUES
 (1)
SET IDENTITY_INSERT Administrador OFF

  1. Execute the above result, the error is shown in the output:
Msg 8106, Level 16, State 1, Line 4
Table 'Administrador' does not have the identity property. Cannot perform SET operation.

Expected behavior

The output of point 3 should have been:

--INSERTs generated by GenerateInsert (Build 6)
--Project page: http://github.com/drumsta/sql-generate-insert
SET NOCOUNT ON
INSERT INTO Administrador
([Id])
VALUES
 (1)

jjmontes avatar Mar 15 '23 16:03 jjmontes