Powerdesigner可以在数据模型中方便地生成测试数据。本文主要简介利用PowerDesigner生成三种基本类型的数据:日期型、中文字符型、数字型。
基本测试表如下:
- if exists (select 1
- from sysobjects
- where id = object_id('DepartDemo')
- and type = 'U')
- drop table DepartDemo
- go
- /*==============================================================*/
- /* Table: DepartDemo */
- /*==============================================================*/
- create table DepartDemo (
- PKID int identity(101,1),
- DName nvarchar(200) null,
- DCode nvarchar(500) null,
- Manager nvarchar(50) null,
- ParentID int null default 0,
- AddUser nvarchar(50) null,
- AddTime datetime null,
- ModUser nvarchar(50) null,
- ModTime datetime null,
- CurState smallint not null default 0,
- Remark nvarchar(500) null,
- F1 int not null default 0,
- F2 nvarchar(300) null,
- constraint PK_DEPARTDEMO primary key (PKID)
- )
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '部门表',
- 'user', @CurrentUser, 'table', 'DepartDemo'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '主键ID',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'PKID'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '名称',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'DName'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '编码',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'DCode'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '主管',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'Manager'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '上级部门',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'ParentID'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '申请人',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'AddUser'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '申请时间',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'AddTime'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '修改人',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'ModUser'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '修改时间',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'ModTime'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '当前状态',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'CurState'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '备注',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'Remark'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '扩展1',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'F1'
- go
- declare @CurrentUser sysname
- select @CurrentUser = user_name()
- execute sp_addextendedproperty 'MS_Description',
- '扩展2',
- 'user', @CurrentUser, 'table', 'DepartDemo', 'column', 'F2'
- go
一、生成日期型测试数据
在“Column Property”中“Detail”,"Create"一个Test Data Profile如下:
然后对相应的列选择该Profile,即可。
二、生成中文字符
思路如下:将需要生成的中文字符导入到一个文件或数据库中,然后从中随机生成。
首先,我们生成一个演示CVS文件,存放一组中文地区名称。
类似地, 我们得先创建一个Profile,假定名称为Profile_CHSChar。
修改属性如下:
然后,生成效果如下:
三、生成数字型测试数据
生成效果如下:
最后,如果你需要导出或导入这些profile,可以如下操作: