sql server 循环插入语句

发布网友发布时间：2022-04-11 02:55

共1个回答

热心网友时间：2022-04-11 04:25

--可以不用循环哦,先建好临时表,和table201401——table201412的表
--下面的代码是把满足table2014%这个条件的表分组统计后插入到临时表
exec sp_MSforeachtable
@command1="insert into #temp select 姓名,substring('?',13,6), count(日期)
from ? group by 姓名",
@whereand=" And o.Name in(Select name From sys.objects Where Type='U' And
Name like 'table2014%'
)"

--当然循环也行

Declare @Name Varchar(100)
Declare @sql Varchar(1000)
DECLARE Cur CURSOR FOR
Select name From sys.objects Where name like 'table2014%'

OPEN Cur
FETCH NEXT FROM Cur INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN

Set @sql='insert into #temp select 姓名,right('''+@Name+''',6), count(日期)
from '+@Name+' group by 姓名'
Exec (@sql)
FETCH NEXT FROM Cur INTO @Name
END
CLOSE Cur
DEALLOCATE Cur

select * from #temp