I create a table and sequence in order to replace identity in the table I use SQL Server 2012 Express but I get this error while I tried to insert data to the table
Msg 11719, Level 15, State 1, Line 2
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, or derived tables.
T-SQL code:
insert into Job_Update_Log(log_id, update_reason, jobid)
values((select next value for Job_Log_Update_SEQ),'grammer fixing',39);
This is my table:
create table Job_Update_Log
(
log_id int primary key ,
update_reason nvarchar(100) ,
update_date date default getdate(),
jobid bigint not null,
foreign key(jobid) references jobslist(jobid)
);
and this is my sequence:
CREATE SEQUENCE [dbo].[Job_Log_Update_SEQ]
AS [int]
START WITH 1
INCREMENT BY 1
NO CACHE
GO
In SQL Server, a sequence is a user-defined schema-bound object that generates a sequence of numbers according to a specified specification. A sequence of numeric values can be in ascending or descending order at a defined interval and may cycle if requested.
The syntax for creating a Sequence Object is: [ START WITH starting_value_of_sequence ] [ INCREMENT BY incremental_index ] [ { MINVALUE [ minimum_value ] } | { NO MINVALUE } ] [ { MAXVALUE [ maximum_value ] } | { NO MAXVALUE } ]
If you right-click on the “Sequences” folder in Microsoft SQL Server Management Studio or on an existing SEQUENCE object, you will be presented with a popup menu with options for creating and maintaining SEQUENCE objects (Figure 2).
Just get rid of the subselect in the VALUES section, like this:
insert into Job_Update_Log(log_id,update_reason,jobid)
values (next value for Job_Log_Update_SEQ,'grammer fixing',39);
Reference: http://msdn.microsoft.com/en-us/library/hh272694%28v=vs.103%29.aspx
Your insert syntax appears to be wrong. You are attempting to use a SELECT
statement inside of the VALUES
section of your query. If you want to use SELECT
then you will use:
insert into Job_Update_Log(log_id,update_reason,jobid)
select next value for Job_Log_Update_SEQ,'grammer fixing',39;
See SQL Fiddle with Demo
I changed the syntax from INSERT INTO VALUES
to INSERT INTO ... SELECT
. I used this because you are selecting the next value of the sequence.
However, if you want to use the INSERT INTO.. VALUES
, you will have to remove the SELECT
from the query:
insert into Job_Update_Log(log_id,update_reason,jobid)
values(next value for Job_Log_Update_SEQ,'grammer fixing',39);
See SQL Fiddle with Demo
Both of these will INSERT
the record into the table.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With