sql 表有没有自增列，插入自增列值

lpxxn

发布于 2018-01-31 10:45:31

1.1K0

发布于 2018-01-31 10:45:31

文章被收录于专栏：技术之路

创建一个表

create table demoTb
(
	ID int identity(1,1) primary key,
	name varchar(30)
)

　　查看有没有自增列有就返回YES

IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N'demoTb'), 'TableHasIdentity')) = 1)
    PRINT 'Yes'
ELSE
    PRINT 'No'

　　如果想插入自增列的值就

declare @tableName varchar(250)
declare @sql nvarchar(1000)
select @tableName = 'demoTb'
select @sql ='SET IDENTITY_INSERT  '+@tableName+' ON' +char(13)+'insert into demoTb(id,name) values(2,''wu'')'
print @sql
exec sp_executesql  @sql
insert into demoTb(id,name) values(1,'wu')
select  * from demoTb