Cannot INSERT VALUES into a Primary Key column, without IDENTITY
Posted on 2013-12-17
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
)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.