Sybase: using identity_insert to insert data in tables with an identity column

I have a stored procedure working which get the name of a table as input and does some processing on the table. It first copies some rows from this table to a tempdb table using select into. It then inserts some additional rows based on some logic. The stored procedure is written in a generic way and doesn’t know upfront on which table it will be working. Some of the tables have an identity column. So after the select into, the tempdb table also has an identity column. The second statement (an insert into) then fails if there is an identity column as it is by default not possible to specify the value for an identity column.

In order to be able to do it, you need to set the identity_insert option to on:

set identity_insert tempdb..mytemptable on
go

But if you set it for a table without any identity column, you get the following error message:

Cannot use ‘SET IDENTITY_INSERT’ for table ‘tempdb..mytemptable’ because the table does not have the identity property.

So you basically first need to check whether an identity column exists for this table and only set the option for this table if it does. This can be done with the following statements:

if exists (select 1 from tempdb..sysobjects o, tempdb..syscolumns c where o.id=c.id and c.status&128=128 and o.name='mytemptable')
begin
	set identity_insert tempdb..mytemptable on
end

(Replace tempdb by the database containing your table and mytemptable by the name of your table)

This first checks whether there is any column for this table which has the status bit 128 set (i.e. is an identity column) and only then sets identity_insert to on for this table.

Another way to do it is:

if next_identity('tempdb..mytemptable') is not null
begin
	set identity_insert tempdb..mytemptable on
end

next_identity return null if there is no identity column in the specified column.

Additionally you should note that identity_insert can only be set to on for a single table in a given database at a time (within one session). If it is already on for a table and you try to set it on for another one, you’ll get the following error message:

Unable to ‘SET IDENTITY_INSERT’ for table ‘tempdb..mytemptable’ because IDENTITY_INSERT or IDENTITY_UPDATE is already ON for the table ‘myothertemptable’ in database ‘tempdb’.

So you first have to set it off for the other table:

set identity_insert tempdb..myothertemptable off
go
set identity_insert tempdb..mytemptable on
go

Of course if you’re doing this all programatically it doesn’t help you much. So you’d first need to find out whether it’s already on for another table. Unfortunately there seems to be no way to get this kind of information from ASE. The only solution to this problem seems to loop through all tables in the current database having an identity column and set identity_insert to off for this table. This can be done using the following stored procedure:

if exists (select 1 from sysobjects where name = 'sp__clearidentityinsert')
	drop procedure sp__clearidentityinsert
go

create procedure sp__clearidentityinsert
as
begin
	declare @table_name varchar(255)
	declare @sqlstatement varchar(512)

	declare id_ins_cursor cursor
	for select distinct o.name from sysobjects o, syscolumns c where o.id=c.id and c.status&128=128
	for read only

	open id_ins_cursor
	fetch id_ins_cursor into @table_name

	while @@sqlstatus = 0
	begin
		set @sqlstatement = 'set identity_insert '+@table_name+' off'
		exec( @sqlstatement )
		fetch id_ins_cursor into @table_name
	end

	close id_ins_cursor
	deallocate cursor id_ins_cursor
end
go

Just execute it without parameters and identity_insert will be set to off for all tables in the current database. The following should now work without problem:

use tempdb
go
set identity_insert myothertemptable on
go
sp__clearidentityinsert
go
set identity_insert mytemptable on
go

If you want to set it for tables in another database than the current database, you’ll need to hardcode the database name in the stored procedure before sysobjects, syscolumns and in @sqlstatement e.g.:

if exists (select 1 from sysobjects where name = 'sp__clearidentityinserttempdb')
	drop procedure sp__clearidentityinserttempdb
go

create procedure sp__clearidentityinserttempdb
as
begin
	declare @table_name varchar(255)
	declare @sqlstatement varchar(512)

	declare id_ins_cursor cursor
	for select distinct o.name from tempdb..sysobjects o, tempdb..syscolumns c where o.id=c.id and c.status&128=128
	for read only

	open id_ins_cursor
	fetch id_ins_cursor into @table_name

	while @@sqlstatus = 0
	begin
		set @sqlstatement = 'set identity_insert tempdb..'+@table_name+' off'
		exec( @sqlstatement )
		fetch id_ins_cursor into @table_name
	end

	close id_ins_cursor
	deallocate cursor id_ins_cursor
end
go

Parametrizing the database name is not so easy since cannot use a parameter for the database name in the select of the cursor. We need to use some tricks using a temporary table and dynamic SQL:

if exists (select 1 from sysobjects where name = 'sp__clearidentityinsert')
	drop procedure sp__clearidentityinsert
go

create procedure sp__clearidentityinsert(@dbname varchar(255) = '')
as
begin
	declare @table_name varchar(255)
	declare @sqlstatement varchar(1024)
	
	if @dbname = '' select @dbname = db_name()	
	
	if exists (select 1 from tempdb..sysobjects where type='U' and name='clearidentityinsert')
	begin
		drop table tempdb..clearidentityinsert
	end
	
	create table tempdb..clearidentityinsert(name longsysname not null)	
	
	set @sqlstatement = 'insert into tempdb..clearidentityinsert select distinct o.name from '+@dbname+'..sysobjects o, '+@dbname+'..syscolumns c where o.id=c.id and c.status&128=128'
	print @sqlstatement 
	exec( @sqlstatement )
	
	declare id_ins_cursor cursor for select name from tempdb..clearidentityinsert for read only
	
	open id_ins_cursor
	fetch id_ins_cursor into @table_name
	
	while @@sqlstatus = 0
	begin
		set @sqlstatement = 'set identity_insert '+@dbname+'..'+@table_name+' off'
		print @sqlstatement 
		exec( @sqlstatement )
		fetch id_ins_cursor into @table_name
	end
	
	close id_ins_cursor
	deallocate cursor id_ins_cursor
	
	drop table tempdb..clearidentityinsert
end
go

If you call sp__clearidentityinsert without a parameter, it will run on the current database. Otherwise it will run on the specified database e.g.: