|
经常要对一个空表添加数据，下面是自动添加测试数据的例子。以sqlserver 为例
1、要生成数据的表
?
?
create table T_INDUSTRY_FINANCE_INSTANCE (
?? ID_????????????????? varchar(32)????????? not null,
?? ARTIFICIAL_PERSON_CODE_ varchar(200)???????? null,
?? COMPANY_NAME_??????? varchar(200)???????? null,
?? STORAGE_BEGINNING_YEAR_ double precision???? null,
?? ASSETS_LIABILITIES_ENDYEAR_ double precision???? null,
?? INCREASE_DECREASE_?? double precision???? null,
?? PAY_WELFARE_ADDEDVALUETAX_ double precision???? null,
?? GROSS_INVEST_??????? double precision???? null,
?? CASH_FLOW_?????????? double precision???? null,
?? OUGHT_TAX_APPEND_??? double precision???? null,
?? REALITY_TAX_APPEND_? double precision???? null,
?? AVERAGE_WORKER_YEAR_ double precision???? null,
?? YEAR_??????????????? varchar(20)????????? null,
?? SEASON_????????????? varchar(20)????????? null,
?? MONTH_?????????????? varchar(20)????????? null,
?? constraint PK_T_INDUSTRY_FINANCE_INSTANCE primary key (ID_)
)
go
?
?
2、存储过程生成里面的varchar?类型的数据?(double 型的太多，第三步用游标来生成，这里省略了CREATE PROCEDURE name as..
)????
? declare @sqltext varchar(8000),@i int,@acount int
set? @acount=1000000
SELECT?? @i?? =?? 0
? WHILE(@i<@acount)
begin
?set @sqltext = convert(varchar(12),@i)+''','''+convert(varchar(12),'''+'t'+convert(varchar(12),ceiling(10*rand()))+''',(@i%4+1))+''',(@i%12+1))
?if @sqltext<>''
begin
???? set @sqltext='insert into t_industry_finance_instance(ID_,ARTIFICIAL_PERSON_CODE_,COMPANY_NAME_,YEAR_,SEASON_,MONTH_) values('''+@sqltext+''')';
print @sqltext
exec(@sqltext)
???? set @sqltext=''
end
??? select @i=@i+1
end
?
?
3、用游标生成里面的double类型的数据?
Declare @Id varchar(20)
Declare @Name varchar(20) --@Name没有用这里
Declare Cur Cursor For
select id_,COMPANY_NAME_ from t_industry_finance_instance
Open Cur
Fetch next From Cur Into @Id,@Name
While @@fetch_status=0
Begin
if(@Id%4=0)
update? t_industry_finance_instance? set STORAGE_BEGINNING_YEAR_=10000*rand(),ASSETS_LIABILITIES_ENDYEAR_=1000*rand(),INCREASE_DECREASE_=1050*rand(),PAY_WELFARE_ADDEDVALUETAX_=2050*rand(),GROSS_INVEST_=1750*rand(),CASH_FLOW_=3050*rand(),OUGHT_TAX_APPEND_=5050*rand(),REALITY_TAX_APPEND_=4550*rand(),AVERAGE_WORKER_YEAR_=6050*rand(),YEAR_='2006' where id_=@Id
else if(@Id%4=1)
update? t_industry_finance_instance? set STORAGE_BEGINNING_YEAR_=10000*rand(),YEAR_='2007' where id_=@Id
else if(@Id%4=2)
update? t_industry_finance_instance? set STORAGE_BEGINNING_YEAR_=10000*rand(),YEAR_='2008' where id_=@Id
else
update? t_industry_finance_instance? set STORAGE_BEGINNING_YEAR_=10000*rand(),YEAR_='2009' where id_=@Id
Fetch Next From Cur Into @Id,@Name End Close Cur Deallocate Cur
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
