【范例】增加大量人员和卡片的脚本

DECLARE @n INT;
DECLARE @uid VARCHAR(36);
DECLARE @cid VARCHAR(36);
DECLARE @typeid VARCHAR(36);
DECLARE @stationid VARCHAR(36);
DECLARE @gid VARCHAR(36);
DECLARE @cardno VARCHAR(20);
DECLARE @usercode VARCHAR(32);
DECLARE @max INT;
DECLARE @gname VARCHAR(256);

SET @gname = 'Test20W';  --表示可添加到哪个分组下面，‘组态系统’可自行修改
SET @max = 200000; --表示可添加人员和卡的最大数量，数值可自行修改

SELECT @gid = id FROM dbo.acscon_group WHERE name = @gname;
SELECT @stationid = id FROM dbo.acscon_group WHERE [type] = 0;
SELECT @typeid = id FROM dbo.acscon_cardtype WHERE [type] = 0;
SET @n = 1;
WHILE @n <= @max
BEGIN
	SET @usercode = 'sa'+convert(VARCHAR,@n);
	IF NOT EXISTS(SELECT id FROM [dbo].[acscon_user] WHERE usercode = @usercode)
	BEGIN
		INSERT INTO [dbo].[acscon_user]([stationid],[usercode],[name],[type],[sex],[pwd],[email],[phone],[country],[province],[city],[address],[photo],[validdatebegin],[validdateend],[status],[descr],[officetel],[creationtime],[certtype],[certno],[duty])
		VALUES(@stationid,@usercode,@usercode,0,0,'fhWuQ7Ia6cM=','','','','','','','','','',1,'','',GETDATE(),0,'','经理');
	END
	SELECT @uid = id FROM [dbo].[acscon_user] WHERE usercode = @usercode;
	
	SET @cardno = convert(VARCHAR,10000000+@n);
	IF NOT EXISTS(SELECT id FROM [dbo].[acscon_card] WHERE cardno = @cardno)
	BEGIN
		INSERT INTO [dbo].[acscon_card]([stationid],[cardno],[typeid],[status],[startdate],[enddate],[cardpwd],[creationtime],[level],[idtype],[idapplication])
		VALUES(@stationid,@cardno,@typeid,1,'2018-05-16 10:20:29.133','2018-12-16 23:50:00.133','rpViimumyT4=',GETDATE(),4,0,1);

	END
	SELECT @cid = id FROM [dbo].[acscon_card] WHERE cardno = @cardno;
	IF NOT EXISTS(SELECT uid FROM [dbo].[acscon_usercard] WHERE uid = @uid and cardid = @cid)
	BEGIN
		INSERT INTO [dbo].[acscon_usercard](uid,cardid) VALUES(@uid,@cid);
	END
	IF NOT EXISTS(SELECT uid FROM [dbo].[acscon_groupmember] WHERE uid = @uid and gid = @gid)
	BEGIN
		INSERT INTO [dbo].[acscon_groupmember](uid,gid,creationtime) VALUES(@uid,'9700F796-1770-E711-BB19-80C16EE6C8EE','2017-08-15 16:23:27.273');
	END
	
	SET @n = @n + 1;
END