递归批量插入数据库 SQL server

---------------------------------------------------------------------插入机柜编号
--申明一个游标
DECLARE MyCursor CURSOR
FOR select F1,F2,F3,F5 from dbo.EXCELCabinetRes where F4='三期机房'
--打开一个游标
OPEN MyCursor
--循环一个游标
DECLARE @CName nvarchar(20),@H_F2 nvarchar(20),@L_F3 nvarchar(20),@isTakeUp nvarchar(20)
FETCH NEXT FROM MyCursor INTO @CName,@H_F2, @L_F3,@isTakeUp
WHILE @@FETCH_STATUS =0
BEGIN
DECLARE @parentName int
select @parentName= ID from db_CabinetRes where cName=@L_F3 and parentName in (
select ID from db_CabinetRes where cName=@H_F2)

INSERT INTO [OpenAuthDB].[dbo].[db_CabinetRes]([guidNum],[longNumber],[cName],[parentName],[isEnable],[isTakeUp],[addDate],[addUserID],[addUserName],[updateDate],[updateUserID],[updateUserName])
values(NEWID(),'',@CName,@parentName,1,@isTakeUp,GETDATE(),'135e48d6-662a-4298-a461-9141eceb8402','admin',GETDATE(),'135e48d6-662a-4298-a461-9141eceb8402','admin')

FETCH NEXT FROM MyCursor INTO @CName,@H_F2, @L_F3,@isTakeUp
END
--关闭游标
CLOSE MyCursor
--释放资源
DEALLOCATE MyCursor

----修改 longNumber
update [db_CabinetRes] set longNumber=CONVERT(nvarchar,bb.pLongNumber) +'.'+CONVERT(nvarchar, [db_CabinetRes].ID) from
(select *,(select longNumber from db_CabinetRes a where a.ID=b.parentName) pLongNumber from db_CabinetRes b where parentName!='0') bb
where [db_CabinetRes].ID=bb.ID

---------------------------------------------------------------------插入U位

--循环生成 52U
DECLARE MyCursor CURSOR
FOR select ID from( select len(longNumber)-len(replace(longNumber,'.','')) lenNum,* from db_CabinetRes) a where a.lenNum=4
--打开一个游标
OPEN MyCursor
--循环一个游标
DECLARE @parentName nvarchar(20)
FETCH NEXT FROM MyCursor INTO @parentName
WHILE @@FETCH_STATUS =0
BEGIN
declare @i int
set @i=1
while @i<=52
begin
INSERT INTO [OpenAuthDB].[dbo].[db_CabinetRes]([guidNum],[longNumber],[cName],[parentName],[isEnable],[isTakeUp],[addDate],[addUserID],[addUserName],[updateDate],[updateUserID],[updateUserName])
values(NEWID(),'',CONVERT(nvarchar, @i)+'U',@parentName,1,0,GETDATE(),'135e48d6-662a-4298-a461-9141eceb8402','admin',GETDATE(),'135e48d6-662a-4298-a461-9141eceb8402','admin')

set @i=@i+1
end
FETCH NEXT FROM MyCursor INTO @parentName
END
--关闭游标
CLOSE MyCursor
--释放资源
DEALLOCATE MyCursor

 

---------------------------------修改容量
update db_CabinetRes set cRongliang=12 where ID in (
select ID from( select len(longNumber)-len(replace(longNumber,'.','')) lenNum,* from db_CabinetRes) a where a.lenNum=4
)

转载于:https://www.cnblogs.com/shunning/p/10937895.html