在做数据库系统开发时,特别是需要对数据库操作进行性能测试及优化时,我们就需要在数据库测试表中插入大量数据以便测试。对于这些数据的插入,这里通过实例展示如何通过存储过程进行实现。
数据库表(userInfo)结构如下:
- CREATE TABLE [dbo].[userInfo] (
- [userID] [int] IDENTITY (1, 1) NOT NULL ,
- [roleType] [int] NULL ,
- [groupID] [int] NULL ,
- [userCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
- [userName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
- [text1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
- [text2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
- [text3] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
- ) ON [PRIMARY]
- GO
CREATE TABLE [dbo].[userInfo] (
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[roleType] [int] NULL ,
[groupID] [int] NULL ,
[userCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[userName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[text1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[text2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[text3] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
存储过程如下(这里是批量插入99000条数据,roleType,groupID两个字段为随机生成的0-5之间的数):
- CREATE PROCEDURE add_UserInfo
- AS
- DECLARE @userCode VARCHAR(30)
- DECLARE @userName VARCHAR(30)
- DECLARE @userCode_base VARCHAR(30)
- DECLARE @count INTEGER
- DECLARE @index INTEGER
- DECLARE @rand1 INTEGER
- DECLARE @rand2 INTEGER
- SET @userCode_base='qs_'
- SET @userName='userName'
- SET @count=100000
- SET @index=10000
- WHILE @index<@count
- BEGIN
- SET @userCode=@userCode_base+CONVERT(VARCHAR,@index)
- SET @rand1=convert(int,rand()*5)
- SET @rand2=convert(int,rand()*5)
- INSERT INTO userInfo (userCode,roleType,groupID,userName,text1,text2,text3)
- VALUES (@userCode,@rand1,@rand2,@userName,'aokei kaol jof','','aokei kaol jof')
- SET @index=@index+1
- END
- GO