当数据的ID不是自增时数据导入的游标遍历

最新推荐文章于 2022-09-16 16:34:17 发布

原创最新推荐文章于 2022-09-16 16:34:17 发布 · 616 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#email #mobile #join #insert #null #c

sql server数据库知识专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用SQL查询批量获取用户信息，并将其导入到数据库中，同时更新相关字段。涉及用户信息包括用户名、性别、手机号、邮箱等，通过游标进行数据处理并插入到目标表中。

    declare @NewID int
    declare @au_ID int
    declare @username varchar(50)
    declare @gender varchar(50)
    declare @mobile varchar(50)
    declare @email varchar(50)
    declare @bday datetime
    declare @lastvisit datetime
    declare @joindate datetime

    DECLARE authors_cursor CURSOR FOR--申明一个游标
    select top 2 discuz.dbo.dnt_users.uid
    from discuz.dbo.dnt_users

    inner join discuz.dbo.dnt_userfields

    on discuz.dbo.dnt_users.uid=discuz.dbo.dnt_userfields.uid where len(email)>1
    and username not in (
    select username from Common.dbo.userinfo
    )
    OPEN authors_cursor --打开游标
    --取出值
    FETCH NEXT FROM authors_cursor INTO @au_ID
    --set @au_all = @au_line + '/'
    --循环取出游标的值
    WHILE @@FETCH_STATUS = 0
    BEGIN
    select @NewID = isnull(MaxID,0) + 1 from [TableMaxIdentity]
    where TableName = 'UserInfo'
    select @username=username,@gender=case(gender) when 1 then '男' when 2 then '女' else '未知' end,
            @mobile=mobile,@email=email,@bday=bday,@lastvisit=lastvisit,@joindate=joindate
    from discuz.dbo.dnt_users
    inner join discuz.dbo.dnt_userfields

    on discuz.dbo.dnt_users.uid=discuz.dbo.dnt_userfields.uid
    where discuz.dbo.dnt_users.uid=@au_ID

    insert into userinfo( ID,userName, password, name,countryID, provincid, cityid, gender, mobile, email, integral,customtype,LegalRepresentative,Job,ExtendInfo,Birthday,LastLoginTime,UpdateDate,Grade,WorkTel,LogoUrl,Status,IsAgent,IsProvider,RegistDate, cust_src_channel, cust_src_type, isMember, isClient, isLinkMan)
    values(@NewID,@username,'5C6EDFB742ADCD00',@username,1001,0,0,@gender
    ,@mobile,@email,0,1,'','','<UserInfo></UserInfo>',@bday,@lastvisit,getdate(),1001,'','',1,0,0,@joindate,1002,1002,1,0,1)


    update [TableMaxIdentity] set MaxID = @NewID where TableName = 'UserInfo'
    FETCH NEXT FROM authors_cursor INTO @au_ID

    END

    CLOSE authors_cursor --关闭游标
    DEALLOCATE authors_cursor --释放游标
    update dnt_users set password='96e79218965eb72c92a549dd5a330112' where email!=null or len(email)>1 and username!='admin'