Make primary key values depend on external variable

[PeDa]

I want to add a primary key to two tables, such that the values in the second table do not overlap those in the first table. The following doesn't work:

DECLARE @last as int
ALTER TABLE dbo.Table1 ADD coKeyfield int PRIMARY KEY IDENTITY
SET @last = (SELECT TOP 1 coKeyfield FROM dbo.Table1 ORDER BY coKeyfield DESC)
ALTER TABLE dbo.Table2 ADD coKeyfield int PRIMARY KEY IDENTITY(@last+1,1)

The last line gives an error message: Incorrect syntax near '@last' Expectiing +, - INTEGER or NUMERIC
Any suggestions welcome (I could of course do it by hand, but this is part of a future conversion, and I want to do everything in one fully automatic script)

 

[zandsc1]

Have you considered making a sequence and using it as the key for both tables?

CREATE SEQUENCE sequence_name(... sequence parameters ...)

CREATE TABLE table1name(table1_id PRIMARY KEY DEFAULT nextval("sequence_name"), ...)

CREATE TABLE table2name(table2_id PRIMARY KEY DEFAULT nextval("sequence_name"), ...)

Each time one of the tables grabs a number from the sequence it increments, ensuring that you won't have overlapping ID's.

I'm of course assuming you were wanting to use a simple integer ID as your primary key...

 

[PeDa]

Thank you. I hadn't yet come across Sequences, but shall look into this option.

 

[PeDa]

Unfortunately I use SQL Server 2008 R2, and Sequences were apparently only introduced in SQL Server 2012. Further research has however led to the following solution:

DECLARE @sql varchar(8000)
DECLARE @myident int
SET @myident = 12345
SET @sql = 'ALTER TABLE dbo.tbConstateringen ADD coKeyfield int PRIMARY KEY IDENTITY(' + CAST(@myident as varchar) + ', 1)'
EXEC(@sql)