This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing the solution to use a single Sequence object between different databases.
We are creating a sequence constraint to assign a sequence object to a particular table, which both are on the same database.
Sometimes, we also require using a Sequence object of an another database.
Unfortunately, you cannot specify another database name when you are creating a default sequence object.
Below is a full demonstration on this:
Create a Sequence object:
|
1 2 3 4 5 6 7 |
USE [AdventureWorks2012] GO CREATE SEQUENCE dbo.seq_TestSequenceNumber AS INT START WITH 1 INCREMENT BY 1; GO |
I created this sequence into AdventureWorks2012 database.
Now use this created Sequence object into TempDB:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE tempdb GO CREATE TABLE dbo.tbl_TestSequence ( ID INT ,Name VARCHAR(255) ,CONSTRAINT pk_tbl_TestSequence_ID PRIMARY KEY(ID) ) GO ALTER TABLE dbo.tbl_TestSequence ADD CONSTRAINT seq_tbl_TestSequence_ID DEFAULT NEXT VALUE FOR [AdventureWorks2012].[dbo].[seq_TestSequenceNumber] FOR ID; GO |
You will get this error message:
The solution is to use Sequence object with INSERT statement:
|
1 2 3 4 5 6 7 8 9 |
USE tempdb GO INSERT INTO dbo.tbl_TestSequence VALUES (NEXT VALUE FOR [AdventureWorks2012].[dbo].[seq_TestSequenceNumber],'Anvesh') ,(NEXT VALUE FOR [AdventureWorks2012].[dbo].[seq_TestSequenceNumber],'Neevan') ,(NEXT VALUE FOR [AdventureWorks2012].[dbo].[seq_TestSequenceNumber],'Roy') GO |
The Result:
