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

Oh, that was stupid... it does work now, why isn't the GO necessary now? — Herman Cordes, Mar 22 '12 at 09:31
Don't forget to reseed identity value: DBCC CHECKIDENT (Genders, RESEED, 5) — Nikola Markovinović, Mar 22 '12 at 09:32
GO is a batch separator. IF ... BEGIN was missing an END, and in part 2 END was orphaned. — Nikola Markovinović, Mar 22 '12 at 09:34
@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? — Herman Cordes, Mar 22 '12 at 09:40

Kaf · Accepted Answer · 2012-03-22T09:39:25.780

6

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

edited Mar 22 '12 at 09:39

answered Mar 22 '12 at 09:30

Kaf

33,101
7
58
78

Cannot set IDENTITY_INSERT in batch

1 Answers1