生成測試數據 T－SQL系列

在xyz表中創建100行

--Method 1. simple DEFAULT values on table

CREATE TABLE xyz
(
col1 int PRIMARY KEY IDENTITY(1,1) NOT NULL,
col2 int NOT NULL DEFAULT 999,
col3 char(10) NOT NULL DEFAULT ''ABCDEFGHIJK''
)
GO

DECLARE @counter int
SET @counter=1
WHILE(@counter<=100)
BEGIN
INSERT xyz DEFAULT valueS
SET @counter=@counter+1
END

SELECT * FROM
xyz

（2） RAND( ) 的一般用法

生成在一定范圍的隨機數

-- 0~9999
(CONVERT(int,RAND()*100000)%10000)

-- -9999~9999
CASE
WHEN (CONVERT(int,RAND()*100)%2=1 THEN
(CONVERT(int,RAND()*100000)%10000*-1)
ELSE (CONVERT(int,RAND()*100000)%10000)
END

-- A~Z
CHAR((CONVERT(int,RAND()*1000)%26)+65) --65 is A

（3） 若想確認每次執行RAND()函數是否產生不同的結果，應該為RAND()指定種子，即把一個整數置於圓括號中。可以用@@spid ，即新建表的object_id，或其它“幾乎隨機”的整數作為種子值。

IF (ISNULL(OBJECT_ID(''random_data''),0))>0
DROP TABLE random_data
GO

CREATE TABLE random_data
(
COL1 int PRIMARY KEY,
col2 int,
col3 char(15)
)
GO

DECLARE @counter int,@col2 int,@col3 char(15)
/*Insert 1000 rows of date */
--Seed Random generator
SELECT @counter=0,@col2=RAND(@@spid+cpu+physical_io)
FROM master..sysprocesses WHERE spid=@@spid

WHILE (@counter<1000)
BEGIN
SELECT @counter=@counter+10, --Sequence numbers by 10
@col2=
CASE --Random integer between -9999 and 9999
WHEN CONVERT(int,RAND()*100)%2=1
THEN (CONVERT(int,RAND()*100000)%10000*-1)
ELSE CONVERT(int,RAND()*100000)%10000
END,
@col3=
CHAR((CONVERT(int,RAND()*1000)%26)+65) --65 is A
+CHAR((CONVERT(int,RAND()*1000)%26)+65)
+CHAR((CONVERT(int,RAND()*1000)%26)+65)
+CHAR((CONVERT(int,RAND()*1000)%26)+65)
+REPLICATE(CHAR((CONVERT(int,RAND()*1000)%26)+65),11)

INSERT random_data valueS(@counter,@col2,@col3)
END
GO

SELECT * FROM random_data WHERE col1<200

用另一種方法實現：

CREATE TABLE random_data
(
COL1 int PRIMARY KEY IDENTITY(10,10) NOT NULL,
col2 int NOT NULL DEFAULT CASE
--Random integer between -9999 and 9999
WHEN CONVERT(int,RAND()*100)%2=1
THEN (CONVERT(int,RAND()*100000)%10000*-1)
ELSE CONVERT(int,RAND()*100000)%10000
END,
col3 char(15) NOT NULL DEFAULT
CHAR((CONVERT(int,RAND()*1000)%26)+65) --65 is A
+CHAR((CONVERT(int,RAND()*1000)%26)+65)
+CHAR((CONVERT(int,RAND()*1000)%26)+65)
+CHAR((CONVERT(int,RAND()*1000)%26)+65)
+REPLICATE(CHAR((CONVERT(int,RAND()*1000)%26)+65),11)
)
GO

DECLARE @counter int
SELECT @counter=1

WHILE (@counter<1000)
BEGIN
INSERT random_data DEFAULT valueS
SET @counter=@counter+1
END
GO

SELECT * FROM random_data WHERE col1<200