CODEWITHSUNDEEP
sql-serversql-server-2008-r2
rgx71
rgx71

Reputation: 857

SEQUENCE in SQL Server 2008 R2

I need to know if there is any way to have a SEQUENCE or something like that, as we have in Oracle. The idea is to get one number and then use it as a key to save some records in a table. Each time we need to save data in that table, first we get the next number from the sequence and then we use the same to save some records. Is not an IDENTITY column.

For example:

[ID] [SEQUENCE ID] [Code]  [Value]
1    1             A       232
2    1             B       454
3    1             C       565

Next time someone needs to add records, the next SEQUENCE ID should be 2, is there any way to do it? the sequence could be a guid for me as well.

Upvotes: 0

Views: 7785

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

As Guillelon points out, the best way to do this in SQL Server is with an identity column.

You can simply define a column as being identity. When a new row is inserted, the identity is automatically incremented.

The difference is that the identity is updated on every row, not just some rows. To be honest, think this is a much better approach. Your example suggests that you are storing both an entity and detail in the same table.

The SequenceId should be the primary identity key in another table. This value can then be used for insertion into this table.

Upvotes: 1

Stuart Blackler
Stuart Blackler

Reputation: 3772

One possible way is to do something like this:

-- Example 1

DECLARE @Var INT
SET @Var = Select Max(ID) + 1 From tbl;
INSERT INTO tbl VALUES (@var,'Record 1')
INSERT INTO tbl VALUES (@var,'Record 2')
INSERT INTO tbl VALUES (@var,'Record 3')

-- Example 2

INSERT INTO #temp VALUES (1,2)
INSERT INTO #temp VALUES (1,2)

INSERT INTO ActualTable (col1, col2, sequence)
SELECT temp.*, (SELECT MAX(ID) + 1 FROM ActualTable)
FROM #temp temp

-- Example 3

DECLARE @var int
INSERT INTO ActualTable (col1, col2, sequence) OUTPUT @var = inserted.sequence VALUES (1, 2, (SELECT MAX(ID) + 1 FROM ActualTable))

The first two examples rely on batch updating. But based on your comment, I have added example 3 which is a single input initially. You can then use the sequence that was inserted to insert the rest of the records. If you have never used an output, please reply in comments and I will expand further.

I would isolate all of the above inside of a transactions.

If you were using SQL Server 2012, you could use the SEQUENCE operator as shown here.

Forgive me if syntax errors, don't have SSMS installed

Upvotes: 0

Consult Yarla
Consult Yarla

Reputation: 1150

This can be done using multiple ways, Following is what I can think of

  1. Creating a trigger and there by computing the possible value

  2. Adding a computed column along with a function that retrieves the next value of the sequence

Here is an article that presents various solutions

Upvotes: 0

Related Questions