Use variable to set identity seed How? 1

[philrm]

Can you use a variable to set the identity seed on an identity column something like this?

DECLARE @maxsitenum int
SET @maxsitenum = (select max (site_id)
From T_SOIL_SITE)
ALTER TABLE sheet1$ ADD
column_b INT IDENTITY (@maxsitenum,1)

 

[zhavic]

Try this

DECLARE @SQL nvarchar(100)
SET @SQL = 'ALTER TABLE sheet1$ ADD column_b INT IDENTITY (' + CAST( @maxsitenum AS varchar ) + ',1)'
EXECUTE ( @SQL )

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[philrm]

This does not work. The command runns successfully but does not add the identity column. What I am trying to do is import an excel speadsheet as a table, Add an identity column to the table based on the maximum value of an ID column + 1 from a table T_soil_site. Hope this makes it a bit clearer

 

[JamesLean]

This works fine for me:

create table #t (c1 varchar(20))

insert #t values ('row 1')
insert #t values ('row 2')
insert #t values ('row 3')

select * from #t

declare @seed int,
  @sql varchar(200)

set @seed = 1

set @sql = 'alter table #t add idcol int identity(' + cast(@seed as varchar) + ', 1)'
exec(@sql)

select * from #t

drop table #t

--James

 

[philrm]

Thanks Very much Guys have it working well now