今天做了一个任务,需要做一个批量插入操作,并且批量时需要生成随机码,批量操作大家都知道,很简单,主要是批量时如何随机生成字符串随机码,经过研究写了以下SQL语句,执行,成功了。
UserInfo表:
CREATE TABLE [dbo].[UserInfo]( [ID] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) NULL, [Password] [nvarchar](50) NOT NULL, [IsDelete] [int] NULL, )
InviteCode_UserBak表:
CREATE TABLE [dbo].[InviteCode_UserBak]( [ID] [INT] IDENTITY(1,1) NOT NULL, [InviteCode] [NVARCHAR](100) NULL, [InviterID] [INT] NULL, [UserType] [INT] NULL, [UsingNum] [INT] NULL, [IsDelete] [INT] NULL)
需要将UserInfo表的数据批量导入到InviteCode_UserBak表,要求是InviteCode字段赋值算法:6位随机字符串(字母+数字)+UserInfoId,开始想到了用游标,但是最后想着是否一个SQL批量语句来执行,写出了以下SQL语句:
INSERT dbo.InviteCode_UserBak (InviteCode , InviterID , UserType, IsDelete) SELECT SUBSTRING(CONVERT(VARCHAR(60),NEWID()),0,7)+CONVERT(VARCHAR(20),ID) AS InviteCode, ID AS InviterID,1 AS UserType,0 AS IsDelete FROM dbo.UserInfo WHERE IsDelete=0 AND id NOT IN( SELECT InviterID FROM dbo.InviteCode_UserBak WHERE UserType=1 AND IsDelete=0)
执行成功,结果如图: