EF4 insert data via scripts

Question

I know that by default EF Code First sets StoreGeneratedPattern = Identit. It's OK. But I want to insert some part of data via sql script. I know that just one table in time can be SET IDENTITY_INSERT Rules ON;

But when I try to run

USE [GameDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--RULES OF GAME
SET IDENTITY_INSERT RulesOfGames ON;
INSERT INTO RulesOfGames(RulesOfGameId, MaxScore, IsPossibleEqualsScore) VALUES(1, 20, 1);
SET IDENTITY_INSERT RulesOfGames OFF;

--KindSport
SET IDENTITY_INSERT RulesOfGames ON;
INSERT INTO KindSports(KindSportId, Name, RulesOfGame_RulesOfGameId) VALUES(1, 'Футбол', 1);
SET IDENTITY_INSERT KindSports OFF;

I get error. And I want to set particular ids for setting particular logic constrains.

Msg 8107, Level 16, State 1, Line 3
IDENTITY_INSERT is already ON for table 'GameDatabase.dbo.KindSports'. Cannot perform SET operation for table 'RulesOfGames'.

How I can resolve my problem?

Try putting a `GO` in between your two insert statement blocks. — Eric J. Price, Mar 26 '13 at 19:35
@Love2Learn `Violation of PRIMARY KEY constraint 'PK_dbo.RulesOfGames'. Cannot insert duplicate key in object 'dbo.RulesOfGames'. The statement has been terminated.` — Ray, Mar 26 '13 at 19:40
Nevermind, the issue is that you set `Identity_Insert` to ON for dbo.KindSports and didn't turn it off and there is a typo in your code in the second insert block, you have identity insert being set to ON for RulesOfGames again instead of KindSports. Fix the typo, highlight the bottom row and run it by itself to clear your identity insert settings and it should work. — Eric J. Price, Mar 26 '13 at 19:40
You can also kill your connection and reconnect since the setting is stored at the session level. — Eric J. Price, Mar 26 '13 at 19:41

score 0 · Accepted Answer · answered Mar 26 '13 at 19:51

SET IDENTITY_INSERT RulesOfGames ON;
INSERT INTO RulesOfGames(RulesOfGameId, MaxScore, IsPossibleEqualsScore) VALUES(1, 20, 1);
SET IDENTITY_INSERT RulesOfGames OFF;

--KindSport
SET IDENTITY_INSERT KindSports ON;
INSERT INTO KindSports(KindSportId, Name, RulesOfGame_RulesOfGameId) VALUES(1, 'Футбол', 1);
SET IDENTITY_INSERT KindSports OFF;

EF4 insert data via scripts

1 Answers1