Cannot INSERT VALUES into a Primary Key column, without IDENTITY

Dear Experts,

I am trying to edit a Survey application through SQL, instead of using the application GUI.  My goal is to add two new lines into an existing table sur_item_answer, preferably without changing anything else, using code to document the changes done by me, and reuse the code later on a different installation of the same survey application.  

The existing table:

CREATE TABLE [dbo].[sur_item_answer](
      [answer_id] [int] NOT NULL,
      [item_id] [int] NOT NULL,
      [answer_text] [nvarchar](255) NULL,
      [answer_value] [int] NULL,
      [order_number] [int] NOT NULL,
      [random_order_number] [int] NULL,
      [default_yn] [char](1) NOT NULL,
      [js_text] [nvarchar](255) NULL,
      [column_type_id] [int] NOT NULL CONSTRAINT [DF_sur_item_answer_column_type_id]  DEFAULT ((0)),
      [column_text] [nvarchar](1500) NULL,
      [validation_ids] [nvarchar](255) NULL,
      [alias] [nvarchar](255) NULL,
 CONSTRAINT [pk_sur_item_answer] PRIMARY KEY CLUSTERED
(
      [answer_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


I can manually give a value for answer_id. But since there are already close to 20 000 answer_ids, I would like to find a method that does not rely on me finding the proper id, and since this number changes continously, old code would never work.

I have tried to submit values using different methods.

1) I have left the PK column empty, hoping for an automatic increment, but the server does not accept that, and I get following error message:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'answer_id', table 'dbo.sur_item_answer'; column does not allow nulls. INSERT fails.

Is this related to answer_id not being an IDENTITY?

2) I can find the proper value for the next answer_id by
SELECT MAX(answer_id)+1 FROM [dbo].[sur_item_answer]
I have tried to use it as a subquery

INSERT [dbo].[sur_item_answer] (answer_id, item_id, answer_text, answer_value, order_number, random_order_number, default_yn, column_type_id)
  VALUES ((SELECT MAX(answer_id)+1 FROM [dbo].sur_item_answer]),1977,'Kjønnsuttrykk',14,12,0,'N',0)

but then I do do get following error message:
Msg 1046, Level 15, State 1, Line 2
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Is there a other way to write the query so that it does insert the proper new value to answer_id column?

As you can see, I am not an expert, and I rely on you to enlighten me, if I am on the wrong track.

Sincerely yours,
tangofil
tangofilAsked:
Who is Participating?
 
Angelp1ayConnect With a Mentor Commented:
> Is this related to answer_id not being an IDENTITY?
Yes. IDENTITY is a method to define how a column can be filled automatically.

You can swap your INSERT VALUES for an INSERT SELECT:
INSERT [dbo].[sur_item_answer] (answer_id, item_id, answer_text, answer_value, order_number, random_order_number, default_yn, column_type_id)
SELECT
    (SELECT MAX(answer_id) + 1 FROM [dbo].sur_item_answer),
    1977,
    'Kjønnsuttrykk',
    14,
    12,
    0,
    'N',
    0

Open in new window


Note: This subquery approach may not be very safe wrt concurrency.
0
 
Angelp1ayCommented:
I think you would be better off adding an identity column.

This is the documentation for the SET IDENTITY_INSERT call (allowing you to insert specific keys into an identity column):
- http://technet.microsoft.com/en-us/library/ms188059.aspx

TEST BEFORE YOU USE ON YOUR PRODUCTION DATA!

Column Swap
I would think you can get away with adding a new column with identity and copying the existing ids across:
- Rename your existing column
- Add a new column 'answer_id' with identity set
- Set identity insert on
- Insert your existing values from old col to new
- Set identity insert off
- Delete the old column

Table Swap
...if that doesn't work or if you prefer you can rename your table, create a new table that's identical except for the extra identity spec and then pull the data across:
- Rename table
- Create new table with old name
- Set identity insert on
- INSERT INTO tname2 FROM tname1
- Set identity insert off
- Delete the old table
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>1) I have left the PK column empty, hoping for an automatic increment,
In that case, you need to define the column as identity.

Replace this
[answer_id] [int] NOT NULL,

Open in new window

with this
[answer_id] [int] identity(1,1) NOT NULL,

Open in new window

and then do not insert a value into answer_id, as it will auto-incriment on its own when new rows are inserted.

Also, the below unique constraint means if there is already a row with answer_id = 5, and an attempt is made to insert a row with the same answer_id = 5, it will fail.
 CONSTRAINT [pk_sur_item_answer] PRIMARY KEY CLUSTERED ( [answer_id] ASC)

Open in new window

0
 
tangofilAuthor Commented:
Concurrency is luckily not a problem, because I am the only one adding items to this table.
0
 
Angelp1ayCommented:
Glad I could help :)
Have a nice day!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.