综合案例
<pre><code class="language-sql">/****** Script for SelectTopNRows command from SSMS ******/
/*删除表并创建新表 Start*/
use [db_pbi]
go
if exists(select * from sysobjects where name='pbi_users')
begin
select 'pbi_users exists'
DROP TABLE [pbi_users] --删除表
--删除表以后重新创建
begin
create table [pbi_users]
(
ID int not null identity(1,1) primary key, --设置为主键和自增长列，起始值为1，每次自增1
userId nvarchar(32) not null,
userPassWord nvarchar(50) not null,
userName nvarchar(20) not null,
[permission] nvarchar(20) not null,
[status] nvarchar(10) not null,
creatTime nvarchar(20) not null,
)
end
end
else
begin
create table [pbi_users]
(
ID int not null identity(1,1) primary key, --设置为主键和自增长列，起始值为1，每次自增1
userId nvarchar(32) not null,
userPassWord nvarchar(50) not null,
userName nvarchar(20) not null,
[permission] nvarchar(20) not null,
[status] nvarchar(10) not null,
creatTime nvarchar(20) not null,
)
end
go
/*删除表并创建新表 End*/
/*删除表中所有数据 Start*/
DELETE db_pbi.dbo.pbi_users
go
/*删除表中所有数据 End*/
/*利用循环，批量增加数据 Start*/
declare @loopNum int
set @loopNum = 5 --总共增加N条数据
declare @ID nvarchar(max)
declare @username nvarchar(20)
declare @permission nvarchar(20)
declare @userIdOrg nvarchar(32)
declare @userId nvarchar(32)
declare @begin int
declare @end int
DECLARE @Xml xml = (SELECT TOP 1 [ID] FROM db_pbi.dbo.pbi_users ORDER BY [ID] DESC FOR XML PATH)
SELECT @ID = CAST(@Xml.query('string(.)') as nvarchar(max))
SET @ID = ISNULL(@ID, 1) --如果没有数据，则@ID默认为1
--SELECT @ID
set @begin = @ID
set @end = @begin + @loopNum
begin
while @begin < @end
begin
declare @rnd1 nvarchar(10)
declare @rnd2 nvarchar(10)
declare @rnd3 nvarchar(10)
set @rnd1 = CAST(round(rand(),0) as nvarchar(max))
set @rnd2 = CAST(round(rand(),0) as nvarchar(max))
set @rnd3 = CAST(round(rand(),0) as nvarchar(max))
--CAST(round(rand(),0) as nvarchar(max))
set @rnd1 = '1'
set @rnd2 = '1'
set @rnd3 = '0'
if @begin = 1
set @permission = '1|1|0'
else if @begin = 2
set @permission = '0|1|0'
else if @begin = 3
set @permission = '1|0|0'
else
set @permission = @rnd1 + '|' + @rnd2 + '|' + @rnd3
set @username = 'test00' + CAST(@begin as nvarchar(max))
set @userIdOrg = (@ID + @username)
set @userId = master.dbo.fn_varbintohexsubstring(0,HashBytes('MD5',@userIdOrg),1,0)
PRINT @userId
INSERT INTO db_pbi.dbo.pbi_users (
[userId]
,[userName]
,[userPassWord]
,[permission]
,[status]
,[creatTime]
) VALUES
(@userId,@username,'test123456', (@permission),'1','1638089003')
set @begin=@begin+1;
continue;
end
end
/*批量增加数据 End*/
go
/*查询数据 Start*/
SELECT TOP 1000 [ID]
,[userId]
,[userName]
,[userPassWord]
,[permission]
,[status]
,[creatTime]
FROM [db_pbi].[dbo].[pbi_users]
/*查询数据 End*/
</code></pre>