初始化数据库数据的一些例子
初始化数据库数据的一些例子
- /****
- 生成机票库测试数据
- @author zdw
- *****/
- ALTER proc [dbo].[init_all]
- as
- --城市数据
- exec dbo.init_city
- --机场数据
- exec dbo.init_airport
- --航空公司
- exec dbo.init_airline
- --其他基础数据
- exec dbo.init_other
- --航班
- exec dbo.init_flight
- --机票与预订
- exec dbo.init_ticket
- ---生成账期账单数据
- exec dbo.init_airtranhis
- /**
- 随机生成指定位数的数字字符串的存储过程
- @author ZDW
- @createtime 2012-01-05
- ***/
- ALTER PROC [dbo].[p_randomN]
- @count INT=18
- as
- SELECT RIGHT(20000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), @count)
- ALTER PROC [dbo].[p_randomName]
- @name VARCHAR(20)='' OUTPUT
- as
- BEGIN
- declare @firstNames varchar(max) /* 保存名的集合 */
- declare @lastNames varchar(max) /* 保存姓的集合 */
- set @firstNames = '梅,芳,海,亮,红,君,军,俊,江,河,湖,波,杰,山,燕,阳,洋,涛,斌,彬,宾,微,伟,威,薇,刚,倩' /* 必须只有一个字 */
- set @lastNames = '刘,方,王,李,赵,孙,钱,胡,易,黄,温,丁,周,魏,陈,曾,涂' /* 必须只有一个字 */
- declare @lastNamesLength int
- declare @firstNamesLength int
- set @lastNamesLength = (LEN(@lastNames) - 1) / 2 + 1
- set @firstNamesLength = (LEN(@firstNames) - 1) / 2 + 1
- declare @firstNameRandom int
- declare @lastNameRandom int
- declare @resultFullNames varchar(max)
- set @resultFullNames = ''
- set @firstNameRandom = CEILING(rand()*@firstNamesLength)
- set @lastNameRandom = CEILING(rand()*@lastNamesLength)
- /*
- 产生第 1 个字,对于的 Index 为 1,注意:这里的 Index 最小值为 1。
- 产生第 2 个字,对于的 Index 为 3,
- 产生第 3 个字,对于的 Index 为 5,
- ...
- */
- set @resultFullNames = @resultFullNames + ( SUBSTRING(@lastNames, @lastNameRandom * 2 - 1, 1) + SUBSTRING(@firstNames, @firstNameRandom * 2 - 1, 1))
- SET @name=@resultFullNames
- --SELECT @name
- end
- /***行程单表生成测试数据开始***/
- TRUNCATE TABLE Travelroute
- DECLARE @i INT=1,@sn VARCHAR(50),@random INT=1,@emid varchar(50)
- WHILE @i <= 2000
- BEGIN
- --生成10位编号
- SELECT @sn=right('0000000000'+cast(@i as varchar),10)
- --生成1-100内的随机整数
- select @random=cast( floor(rand()*100) as int)
- select top 1 @emid=emid from expressmail order by NEWID()
- INSERT Travelroute(tlid,emid,sn,batsn,createtime,usid,fee,status)
- SELECT REPLACE(LOWER(NEWID()),'-',''),@emid,@sn,'001',GETDATE(),1,@random*100,1
- SET @i = @i +1
- END
- SELECT * FROM Travelroute
- /***行程单表生成测试数据结束***/
- /***旅客表生成测试数据开始***/
- TRUNCATE TABLE Passenger
- DECLARE @name VARCHAR(10),@ic VARCHAR(50),@phone VARCHAR(20)
- set @i =0
- WHILE @i <= 1000
- BEGIN
- --生成18位身份证号码
- select top 1 @ic=(abs(checksum(newid()))%9+1)*100000000000000000+abs(checksum(newid()))%100000000000000000
- from sysobjects
- --生成11位手机号
- select top 1 @phone=(abs(checksum(newid()))%9+1)*10000000000+abs(checksum(newid()))%10000000000
- --生产随机姓名
- EXEC p_randomName @name OUTPUT
- INSERT Passenger(psid,name,type,sn,phone,ptype,birth,psid2)
- SELECT REPLACE(LOWER(NEWID()),'-',''),@name,1,@ic,@phone,1,null,0
- SET @i = @i +1
- END
- SELECT * FROM Passenger
- /***旅客表生成测试数据结束***/
评论暂时关闭