递归插入mysql数据库_递归批量插入数据库 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

)