现在有2表,
tab_user 用户表
tab_JiFen 积分表现在我要做的事情是
为用户表中每个用户,
各执行10次,系统送积分的操作。----
基本的2个语句是:
1) 查出所有的用户ID
select uId from tab_user where uId <>02) 系统送积分表 (0表示系统用户)
insert into tab_JiFen (sendId,receiveId,fDatetime) values(0,@uId,getdate())
tab_user 用户表
tab_JiFen 积分表现在我要做的事情是
为用户表中每个用户,
各执行10次,系统送积分的操作。----
基本的2个语句是:
1) 查出所有的用户ID
select uId from tab_user where uId <>02) 系统送积分表 (0表示系统用户)
insert into tab_JiFen (sendId,receiveId,fDatetime) values(0,@uId,getdate())
select uId from tab_user where uId <>0
这个结果插入临时表,加个自增列,然后循环起
--其实你可以直接批量插入
insert into tab_JiFen (sendId,receiveId,fDatetime)
select 0,uId,getdate() from tab_user where uId <>0
set @uId=1
begin
while @uId<=10
insert into tab_JiFen (sendId,receiveId,fDatetime) values(0,@uId,getdate())
set @uId=@uId+1
end
set @uId=1
begin
while @uId<=10
insert into tab_JiFen (sendId,receiveId,fDatetime) values(0,@uId,getdate())
set @uId=@uId+1
end
insert into tab_JiFen (sendId,receiveId,fDatetime)
select 0,uId,getdate() from tab_user where uId <>0go 10
go
Create table tab_user (uId int)
go
insert tab_user
select top 10 number from master.dbo.spt_values where type='P' order by number
/*
0
1
2
3
4
5
6
7
8
9
*/
go
Create table tab_JiFen(sendId int,receiveId int,fDatetime datetime)
go
--执行
insert into tab_JiFen (sendId,receiveId,fDatetime)
select 0,uId,getdate() from tab_user where uId <>0go 10
go
select * from tab_JiFen
/*--(90 row(s) affected)sendId receiveId fDatetime
0 1 2011-10-24 21:33:23.537
0 2 2011-10-24 21:33:23.537
0 3 2011-10-24 21:33:23.537
0 4 2011-10-24 21:33:23.537
0 5 2011-10-24 21:33:23.537
.....
0 8 2011-10-24 21:33:23.583
0 9 2011-10-24 21:33:23.583
*/
我在Asp.net 里的代码执行会如下,是可以实现功能的:可否能用Sql语句,也写个方法一样一次性执行得到结果呢?