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

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