Cannot set IDENTITY_INSERT in batch

Question

Case

Currently I'm working on database seeding script, executed with sqlcmd. For example this script sample:

IF (SELECT COUNT(*) FROM Genders)=0
BEGIN
   PRINT N'Seeding table Genders...'

   SET IDENTITY_INSERT Genders ON
   GO

   INSERT INTO Genders (GenderId, Description) VALUES (0, 'Onbekend');
   INSERT INTO Genders (GenderId, Description) VALUES (1, 'Vrouw');
   INSERT INTO Genders (GenderId, Description) VALUES (2, 'Man');
   INSERT INTO Genders (GenderId, Description) VALUES (3, 'Onzijdig');
   INSERT INTO Genders (GenderId, Description) VALUES (4, 'Vrouwman');
   INSERT INTO Genders (GenderId, Description) VALUES (5, 'Manvrouw');

   SET IDENTITY_INSERT Genders OFF
END
GO

Problem

However, if I execute it with sqlcmd mode in SQL Server Management Studio, it gives me this errors:

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'ON'.

Msg 102, Level 15, State 1, Line 10 Incorrect syntax near 'END'.

Googled some, but can't figure out what I'm doing wrong. Without the IF/BEGIN/END if does work, but I like to perform the check first.

Questions:

• Anything I'm doing wrong?
• If impossible, any workaround available?

Thanks in advance!!

Hospitalet

Remove GO after set identity_insert (...) – Sirloin

Does it work without the GO? – Thaler

Oh, that was stupid... it does work now, why isn't the GO necessary now? – Hospitalet

Don't forget to reseed identity value: DBCC CHECKIDENT (Genders, RESEED, 5) – Sirloin

GO is a batch separator. IF ... BEGIN was missing an END, and in part 2 END was orphaned. – Sirloin

@NikolaMarkovinović Thank for clearing that up, wasn't aware of that! About the reseed issue, I checked it recognizes the current identity value to be 5 already, why should I reseed the identity value? – Hospitalet

Sorry Monty, in them olden days you had to. – Sirloin

Answer 1

You cannot have GO within BEGIN and END. Try following

SET IDENTITY_INSERT Genders ON

IF (SELECT COUNT(*) FROM Genders)=0
BEGIN
   PRINT N'Seeding table Genders...'

   INSERT INTO Genders ...

END

SET IDENTITY_INSERT Genders OFF
GO

Dric