--判断是否存在你要创建的表,如果存在,则删除.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yourtablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[yourtablename]
GO--实际上只需要下面的SQL语句即可.
CREATE TABLE [dbo].[yourtablename] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yourtablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[yourtablename]
GO--实际上只需要下面的SQL语句即可.
CREATE TABLE [dbo].[yourtablename] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
我的意思是创建一个表
比如:里面有列name,还有一个自动增长的列为主键(不是直接数字增长,而是有前缀增长).
以后插入数据只要输入name列就好,主键列不用填
能实现吗?
(
id int not null identity(1,1),
acyear varchar(50) not null,
)
这个id列就是自动增长的,
我只想他自动增长时前面都有个table_字样..
那请问触发器怎么弄呢..
as
begin
declare @id as varchar(20)
select @id = max(id) from ta
if @id is null
set @id = 'table_1'
else
set @id = 'table_' + rtrim(cast(cast(substring(@id , 7 , len(@a) - 7) as int) + 1) as varchar(20))
insert into ta(id) values('table_1')
end随手写的,不知道对不对?
create table test
(
Aid int not null identity(1,1),
id as 'table_'+cast(Aid as varchar(10)),
acyear varchar(50) not null
)insert into test select '2005' union all select '2006'select id,acyear from test --这里就不必写上Aid,(Aid是一个中间列而已,不是最终目标)
/*结果
table_1 2005
table_2 2006
*/