Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server 2012 sequence

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
like image 649
danarj Avatar asked Oct 05 '12 11:10

danarj


People also ask

Does SQL Server have sequences?

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.

How do you sequence in SQL?

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 } ]

How do I view a sequence in SQL Server Management Studio?

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).


2 Answers

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

like image 54
TToni Avatar answered Oct 13 '22 22:10

TToni


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.

like image 7
Taryn Avatar answered Oct 13 '22 20:10

Taryn