CREATE TABLE [dbo].[sequence](
	[tbname] [varchar](60) NOT NULL,
	[name] [varchar](60) NOT NULL,
	[reserved] [bigint] NOT NULL,
	[maxvalue] [bigint] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[asset](
	[assetnum] [varchar](60) NOT NULL,
	[assetid] [bigint] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[vendor](
	[vendornum] [varchar](60) NOT NULL,
	[vendorid] [bigint] NOT NULL
) ON [PRIMARY]

INSERT INTO sequence(tbname, name, reserved, maxvalue)
SELECT asset, assetid, 50, 40 UNION ALL
SELECT vendor, vendorid, 50, 40 

insert into asset (assetnum, assetid)
select 'Asset1', 38 union all
select 'Asset2', 39 union all
select 'Asset3', 40

insert into vendor (vendornum, vendorid)
select 'Vendor1', 38 union all
select 'Vendor2', 39 union all
select 'Vendor3', 40 union all
select 'Vendor4', 55 

--create temp table
create table #sequence
(tbname varchar(50),
colname varchar(50),
reserved numeric,
value numeric default 0,
sql varchar(300),
seq numeric)

--populate basic info into temp table
insert into #sequence (tbname, colname, reserved) select  tbname, name, reserved  from sequence

--populate sequence numbers
declare @seq numeric = 0
update #sequence set @seq = seq = @seq + 1

--build preliminary dynamic sql
update #sequence set sql = 'select max(' + colname + ') from '+ tbname

--populate value column with max value from the tables
declare @count numeric = 0
declare @sqlstmt varchar(300)
declare @maxvalue numeric
while @count <= (select max(seq) from #sequence)
	begin
		set @sqlstmt = 'update #sequence set value = (' + (select SQL from #sequence where seq = @count) + ') where seq = ' + cast(@count as varchar(10))
		exec (@sqlstmt)
		set @count = @count + 1
	end

--Build and execute dynamic sql to update sequence
declare @tblname varchar(50) 
declare @colname varchar(50)
set @count = 1

delete from #sequence where value is null
delete from #sequence where value < reserved


while @count <= (select max(seq) from #sequence)
	begin
		set @tblname = (select tbname from #sequence where seq = @count)
		set @colname = (select colname from #sequence where seq = @count)
		set @maxvalue = (select value from #sequence where seq = @count) + 1

		set @sqlstmt = 'update sequence set reserved = ' +  cast(@maxvalue as varchar(10)) + ' where tbname = ''' + @tblname + ''' and name = ''' + @colname + ''''
		exec (@sqlstmt)
		set @count = @count + 1
	end	

drop table #sequence

CREATE TRIGGER Trig_VendorCheck
ON Vendor
FOR INSERT
AS
BEGIN

DECLARE @MaxID int

SELECT @MaxID=MAX(vendorid) 
FROM INSERTED


UPDATE Sequence
SET reserved = @MaxID +1 
WHERE tbname = 'vendor'
AND name ='vendorid'
AND reserved < @MaxID

END

quote:

---

Originally posted by visakh16

isnt it matter of putting an insert trigger for this on vendor ? something like

CREATE TRIGGER Trig_VendorCheck
ON Vendor
FOR INSERT
AS
BEGIN

DECLARE @MaxID int

SELECT @MaxID=MAX(vendorid) 
FROM INSERTED


UPDATE Sequence
SET reserved = @MaxID +1 
WHERE tbname = 'vendor'
AND name ='vendorid'
AND reserved < @MaxID

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

so you want same rule to be applied for all 400 + tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Transact Charlie

you could have your developers insert only values with negative numbers? That's assuming your ID seed starts at 0. That way you'd never encounter a PK conflict.

---

quote:

---

Originally posted by Transact Charlie
However,

Why wouldn't you use an IDENTITY auto incremental key here?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

---

IF OBJECT_ID('TempDB.dbo.#seq', 'U') IS NOT NULL BEGIN
	DROP TABLE dbo.#seq;
END;

CREATE TABLE dbo.#seq(tbname varchar(60), colname varchar(60), maxid bigint);

DECLARE @nsql nvarchar(max)='INSERT INTO #seq(tbname, colname, maxid)' + CHAR(13) + CHAR(10);

SELECT @nsql = @nsql + 'SELECT ''' + tbname + ''', ''' + name + ''', max('+name+') as MaxID FROM dbo.'+tbname+' UNION ' + CHAR(13) + CHAR(10)
FROM dbo.sequence;

/* get rid of extra "UNION" and CRLF at the end of the dynamic string */
SET @nsql = LEFT(@nsql, len(@nsql)-8);

exec sp_executesql @nsql;

/* check values */
--SELECT * FROM dbo.#seq;

UPDATE seq
SET seq.reserved = t.maxid
FROM dbo.sequence seq
INNER JOIN dbo.#seq t
	ON seq.tbname = t.tbname AND seq.name = t.colname;