1

I get the following error message:

{returnResult=Cannot insert explicit value for identity column in table 'player' when IDENTITY_INSERT is set to OFF.;Result=Cannot insert explicit value for identity column in table 'player' when IDENTITY_INSERT is set to OFF.;}

I'm currently trying to auto-increment a player_id in a player table every time a new player is created. I set this up in Windows SQL Server Management Studio.

What should my query look like?

INSERT INTO player (player_id, player_type_id)
VALUES('','1')

I have one manual value, but I want the user_id to be unique each time it is created. All help greatly appreciated!

Martin Erlic
  • 5,467
  • 22
  • 81
  • 153

2 Answers2

1

You have to set identity insert on (that is if you want to supply your own identity value instead of asking MYSQL to generate one for you )

SET IDENTITY_INSERT Table1 ON

Then execute your query..by supplying your own value for that column

and then turn it off after that..(Since you want only one manual insert done)

SET IDENTITY_INSERT Table1 OFF
cableload
  • 4,215
  • 5
  • 36
  • 62
1

If you have correctly set the player_id field up to auto increment, you simply leave that column out of your INSERT statement, and just supply the other values.

INSERT INTO player (player_type_id)
VALUES('1')

If you believe your column is not currently set to auto increment, have a look at this.

Community
  • 1
  • 1
William
  • 6,332
  • 8
  • 38
  • 57
  • This worked perfectly. I wasn't sure about leaving the auto-incremented column blank. Thanks! Now I wonder how I would query this player_id without explicitly calling the value. – Martin Erlic Apr 21 '16 at 14:22
  • 1
    No problem, could you elaborate or ask a new question, I'm not sure what you mean by that? – William Apr 21 '16 at 14:24
  • Sure, I posted it here: http://stackoverflow.com/questions/36772776/find-a-value-based-on-the-most-recently-created-row-in-a-table-sql-server – Martin Erlic Apr 21 '16 at 14:33