/* Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) 愿和大家共同进步 如有雷同、实属巧合 ●●●●●2009-09-07 08:50:34.700●●●●● ★★★★★soft_wsx★★★★★ */ --编号处理-按日期生成单据编号 --创建得到当前日期的视图,以便在用户定义函中可以获取当前日期 create view dbo.v_getdate as select dt=convert(char(6),getdate(),12) --年-月-日 yy-mm-dd go--得到新编号的编号 alter function dbo.f_nextbh() returns nvarchar(20) as begin declare @dt char(6) select @dt=dt from dbo.v_getdate return(select 'SBA'+right(10000001+isnull(right(max(bh),6),0),6) --return(select @dt+right(10000001+isnull(right(max(bh),6),0),6) --当前日期+6位流水号(或指定字符加流水号 from tb with(xlock,paglock) where bh like 'SBA%') end go --在表中应用函数 if OBJECT_ID('tb') is not null drop table tb create table tb(bh nvarchar(20) default dbo.f_nextbh(),col int) goinsert tb(col) values(1) insert tb(col) values(2) insert tb(col) values(3) insert tb(col) values(4) insert tb(col) values(5) insert tb(col) values(6) insert tb(bh,col) values(dbo.f_nextbh(),14) select * from tb/* bh col SBA000001 1 SBA000002 2 SBA000003 4 SBA000004 5 SBA000005 6 SBA000006 14 */这样吧!
如果想插入自增列: set identity_insert MyTable on INSERT INTO MyTable (显示输入列名不可或缺) -----这个很重要 SELECT * FROM MyTableBackup set identity_insert MyTable off
--创建测试表 CREATE TABLE t1(ID int IDENTITY,A int) GO --插入记录 INSERT t1 VALUES(1) GO--1. 将IDENTITY(标识)列变为普通列 ALTER TABLE t1 ADD ID_temp int GOUPDATE t1 SET ID_temp=ID ALTER TABLE t1 DROP COLUMN ID EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN' INSERT t1 VALUES(100,9) GO--2. 将普通列变为标识列 CREATE TABLE t1_temp(ID int,A int IDENTITY) SET IDENTITY_INSERT t1_temp ON INSERT t1_temp(ID,A) SELECT * FROM t1 SET IDENTITY_INSERT t1_temp OFF DROP TABLE T1 GOEXEC sp_rename N't1_temp',N't1' INSERT t1 VALUES(109999) GO--显示处理结果 SELECT * FROM t1 /*--结果: ID A ----------------- ----------- 1 1 100 9 109999 10 --*/
允许将显式值插入表的标识列中。 Transact-SQL 语法约定语法
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }
--1、create table tablename(id int identity(1,1))go insert into tablename default values go select * from tablenamegodrop table tablename go-- 2、--还用上个表。set identity_insert tablename on insert tablename(id) select 2 set identity_insert tablename off
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-07 08:50:34.700●●●●●
★★★★★soft_wsx★★★★★
*/
--编号处理-按日期生成单据编号
--创建得到当前日期的视图,以便在用户定义函中可以获取当前日期
create view dbo.v_getdate
as
select dt=convert(char(6),getdate(),12) --年-月-日 yy-mm-dd
go--得到新编号的编号
alter function dbo.f_nextbh()
returns nvarchar(20)
as
begin
declare @dt char(6)
select @dt=dt from dbo.v_getdate
return(select 'SBA'+right(10000001+isnull(right(max(bh),6),0),6)
--return(select @dt+right(10000001+isnull(right(max(bh),6),0),6) --当前日期+6位流水号(或指定字符加流水号
from tb with(xlock,paglock)
where bh like 'SBA%')
end
go
--在表中应用函数
if OBJECT_ID('tb') is not null drop table tb
create table tb(bh nvarchar(20) default dbo.f_nextbh(),col int)
goinsert tb(col) values(1)
insert tb(col) values(2)
insert tb(col) values(3)
insert tb(col) values(4)
insert tb(col) values(5)
insert tb(col) values(6)
insert tb(bh,col) values(dbo.f_nextbh(),14)
select * from tb/*
bh col
SBA000001 1
SBA000002 2
SBA000003 4
SBA000004 5
SBA000005 6
SBA000006 14
*/这样吧!
如果想插入自增列: set identity_insert MyTable on
INSERT INTO MyTable (显示输入列名不可或缺) -----这个很重要
SELECT * FROM MyTableBackup
set identity_insert MyTable off
CREATE TABLE t1(ID int IDENTITY,A int)
GO
--插入记录
INSERT t1 VALUES(1)
GO--1. 将IDENTITY(标识)列变为普通列
ALTER TABLE t1 ADD ID_temp int
GOUPDATE t1 SET ID_temp=ID
ALTER TABLE t1 DROP COLUMN ID
EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN'
INSERT t1 VALUES(100,9)
GO--2. 将普通列变为标识列
CREATE TABLE t1_temp(ID int,A int IDENTITY)
SET IDENTITY_INSERT t1_temp ON
INSERT t1_temp(ID,A) SELECT * FROM t1
SET IDENTITY_INSERT t1_temp OFF
DROP TABLE T1
GOEXEC sp_rename N't1_temp',N't1'
INSERT t1 VALUES(109999)
GO--显示处理结果
SELECT * FROM t1
/*--结果:
ID A
----------------- -----------
1 1
100 9
109999 10
--*/
允许将显式值插入表的标识列中。 Transact-SQL 语法约定语法
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }
insert into tablename default values
go
select * from tablenamegodrop table tablename
go--
2、--还用上个表。set identity_insert tablename on
insert tablename(id) select 2
set identity_insert tablename off