批量插入数据:
定义变量,执行循环,将int转化为varchar。例如:convert(varchar(10),@bmc)
declare
@name
varchar
(
50
);
declare @pwd varchar ( 10 );
set @pwd = ' 123456 ' ;
declare @bmc int ;
set @bmc = 101 ;
while ( cast ( @bmc as int ) < 120 )
begin
set @name = ' TV8 ' + convert ( varchar ( 10 ), @bmc );
insert into dbo.T_TopView_Admin(F_Psnid,F_status,F_BroPwd,F_Edittime,F_StartTime,F_EndTime,F_UserType) values ( ' TV8 ' + convert ( varchar ( 10 ), @bmc ), 1 , ' 123456 ' , getdate (), 20080320 , 20090319 , 1 )
set @bmc = @bmc + 1
end
declare @pwd varchar ( 10 );
set @pwd = ' 123456 ' ;
declare @bmc int ;
set @bmc = 101 ;
while ( cast ( @bmc as int ) < 120 )
begin
set @name = ' TV8 ' + convert ( varchar ( 10 ), @bmc );
insert into dbo.T_TopView_Admin(F_Psnid,F_status,F_BroPwd,F_Edittime,F_StartTime,F_EndTime,F_UserType) values ( ' TV8 ' + convert ( varchar ( 10 ), @bmc ), 1 , ' 123456 ' , getdate (), 20080320 , 20090319 , 1 )
set @bmc = @bmc + 1
end
批量插入数据,这个实例是用户两个数据结构类似的两张表进行数据导入
declare
@fid
int
;
set @fid = 1 ;
while @fid < 100
begin
insert into T_Mail_Test_Attach(F_ID,F_ContentID,F_AttachName,F_AttachSize,F_AttachAddress,F_EditTime) select top 1 F_ID,F_MailID,F_AttachName,F_AttachSize,F_AttachAddr,F_EditTime from dbo.T_TLMail_Attach where F_ID > @fid order by F_ID asc
set @fid = @fid + 1
end
set @fid = 1 ;
while @fid < 100
begin
insert into T_Mail_Test_Attach(F_ID,F_ContentID,F_AttachName,F_AttachSize,F_AttachAddress,F_EditTime) select top 1 F_ID,F_MailID,F_AttachName,F_AttachSize,F_AttachAddr,F_EditTime from dbo.T_TLMail_Attach where F_ID > @fid order by F_ID asc
set @fid = @fid + 1
end