3

Situation

I have a SQL script that I need to push some basic data to my database. Therefore I use the following script (and others). I want to provide manually an primary key for the rows I manually create.

Problem

If I execute the script it will say that I need to enable IDENTITY_INSERT. I added this row SET IDENTITY_INSERT UserGroups ON; as many examples use but it still will give the same error when I have added it.

SET IDENTITY_INSERT UserGroups ON;  
GO 

INSERT INTO UserGroups VALUES (0, 0);

Error when running script:

An explicit value for the identity column in table 'UserGroups' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Question

Do I need to change something in my database as well or is there something else what I forget in the script to add manually a primary key?

Details

I use SQL Server 2016 Management Studio.

I use DDL Script for SQL scripts

I work with Entity Framework.

I got two columns in this table

  1. Primary key: Id
  2. int: GroupHeadId
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Timon Post
  • 2,779
  • 1
  • 17
  • 32

2 Answers2

8

As the error states, you need a column list. 

INSERT INTO UserGroups (Id, GroupHeadId)
VALUES (0,0)
Jacob H
  • 2,455
  • 1
  • 12
  • 29
7

The error message is telling you the problem.

An explicit value for the identity column in table 'UserGroups' can only be specified when a column list is used and IDENTITY_INSERT is ON.

You're not using a column list. Specify the columns.

Daniel Mann
  • 57,011
  • 13
  • 100
  • 120
  • 1
    The both are good anwers I chose to accept the other one because that was code that I could paste and what worked directly. If I could I would accept this one as well :}. – Timon Post May 17 '17 at 20:52