透過SQL語法就可以產生資料表測試資料

最近同事看到資料表內有好多筆資料，問我說是用什麼工具產生匯入的呢?我跟他說我透過SBP內建的產生匯入資料表SQL亂數語法產生匯入的。

系統預先產生的sql語法

依照需求修改後的sql語法

Sbp亂數函數有底下功能

產生起訖之間亂數整數: select [comm].[fn_rand](@min_num, @max_num)

產生文字亂數：select comm.fn_rand_str_between(@min_length,@max_length)

產生日期亂數：select comm.fn_rand_date(@min_num,@max_num)

產生本國人ID亂數：select comm.fn_rand_idn()

產生自訂清單亂數：select comm.fn_rand_str_in(‘Y,N’)

產生ID不重複亂數清單：select idn from [comm].[fn_rand_idn_list](@num)

產生匯入資料表SQL亂數語法：select [comm].[fn_gen_insert_rand_value_ext_sql]

以下針對這幾個函數詳細說明

雖然sql server本身有提供rand()函數，但是若放在查詢資料表時欄位時，雖然重複執行產生的亂數值都不一樣，但每一筆資料產生都會是相同的內容，我利用rand()衍生產生幾個自訂函數如下

產生起訖之間亂數整數

declare @min_num int=1
declare @max_num int=10
select [comm].[fn_rand](@min_num, @max_num)
select [comm].[fn_rand](@min_num, @max_num)
select [comm].[fn_rand](@min_num, @max_num)

函數說明：由於sql function中，不允許使用rand()系統函數，所以必須改使用view的方式取亂數值，所以我額外建立了comm.vw_rand 檢視表如下，該檢視表使用到系統rand()函數，

若在function內使用rand()會出現錯誤訊息

產生起訖文字個數亂數

declare @min_length int=10 —最小文字長度
declare @max_length int=20 —最大文字長度
select comm.fn_rand_str_between(@min_length,@max_length)
select comm.fn_rand_str_between(@min_length,@max_length)
select comm.fn_rand_str_between(@min_length,@max_length)

函數說明：系統使用comm.fn_rand()產生要產生的長度，當產生的長度符合時會傳產生結果

產生起訖日期亂數

declare @min_num int=20190201 –最小日期
declare @max_num int=20190331 –最大日期
select comm.fn_rand_date(@min_num,@max_num)
select comm.fn_rand_date(@min_num,@max_num)
select comm.fn_rand_date(@min_num,@max_num)

函數說明：系統使用isdate檢查亂數產生的數字是否為有效的日期格式，若不是重新產生，否則回傳。

產生本國人ID亂數

select comm.fn_rand_idn()
select comm.fn_rand_idn()
select comm.fn_rand_idn()

函數說明：以上系統採用自訂函數comm.fn_check_idn來檢查產生的內容是否為合法的idn，若不是重新產生，否則會傳正確的idn。

產生自訂清單亂數

declare @list nvarchar(max)

set @list=N’Y,N’ –自訂清單
select comm.fn_rand_str_in(@list)
set @list="
select @list+=case when @list=" then code_no else ‘,’+code_no end from comm.tb_code where code_type=’002’
select @list –從資料表內取得清單
select comm.fn_rand_str_in(@list)
select comm.fn_rand_str_in(@list)
select comm.fn_rand_str_in(@list)

產生ID不重複亂數清單

declare @num int=5 –產生5筆不重複id
select idn from [comm].[fn_rand_idn_list](@num) 
select idn from [comm].[fn_rand_idn_list](@num) 
select idn from [comm].[fn_rand_idn_list](@num)

產生亂數匯入到資料表的insert sql 語法

執行底下語法，並將執行結果產生的sql語法再次執行一次，即可產生匯入亂數insert sql 語法

查詢語法

declare @schemaname varchar(10)=’comm’ –結構描述
declare @tablename varchar(50)=’tb_code’ –資料表名稱
declare @num_of_count int=10 –產生匯入筆數
select [comm].[fn_gen_insert_rand_value_ext_sql] (@schemaname,@tablename,@num_of_count)

查詢結果

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT ‘

SET QUOTED_IDENTIFIER ON
INSERT INTO comm.tb_code with(tablock)
(code_type,code_no,code_desc,creator,create_time,modifier,last_update_time)’ as data
UNION ALL
select cast(  case when no=1 then " else ‘union all’ end +char(13)+char(10) +’ select ‘+""+comm.fn_rand_str_between(1,3)+""–code_type 代碼型態
+’,’+""+comm.fn_rand_str_between(1,6)+""–code_no 代碼
+’,’+’N"‘+comm.fn_rand_str_between(1,100)+""–code_desc 代碼說明
+’,’+""+comm.fn_rand_str_between(1,255)+""–creator 建立者
+’,’+""+cast(comm.fn_rand_date(19110101,21001231) as varchar)+""–create_time 建立時間
+’,’+""+comm.fn_rand_str_between(1,255)+""–modifier 最後更新者
+’,’+""+cast(comm.fn_rand_date(19110101,21001231) as varchar)+""–last_update_time 最後更新時間
+" as varchar(max)) as data
from comm.fn_get_list(1,10)

以下我使用一個範例來說明

1. 目的資料表：dbo.TB_CUSTOMER(客戶基本資料檔)

欄位編號	欄位	欄位說明	資料型態	NULL(空值)	PK
1	idn	身分證字號	varchar(10)	N	Y
2	cust_name	客戶姓名	nvarchar(10)	N
3	birthday	生日	varchar(8)	Y
4	gender	性別	char(1)	N
5	height	身高	tinyint	N

透過[comm].[fn_gen_insert_rand_value_ext_sql]產生10筆匯入目的資料表亂數清單

透過[comm].[fn_rand_idn_list] 產生10筆唯一不重複的ID亂數清單

透過[comm].[fn_rand_str_between]產生姓名亂數(長度6~10碼的文字內容)

透過[comm].[fn_rand_date]產生生日日期亂數(19700101~20190830)

透過[comm].[fn_rand_str_in]產生性別亂數(F,M)

透過[comm].[fn_rand]產生身高亂數(100~200)

執行將產生亂數語法匯入到dbo.TB_CUSTOMER內

步驟一：先在資料庫建立以上資料表

步驟二:使用[comm].[fn_gen_insert_rand_value_ext_sql]產生10筆測試資料

declare @schemaname varchar(10)=’dbo’ –結構描述

declare @tablename varchar(50)=’tb_customer’ –資料表名稱

declare @num_of_count int=10 –產生匯入筆數

select [comm].[fn_gen_insert_rand_value_ext_sql] (@schemaname,@tablename,@num_of_count)

執行結果如下，系統預設依資料表型態使用相對應的函數。

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT ‘

INSERT INTO dbo.tb_customer with(tablock)
(idn,cust_name,birthday,gender,height)’ as data
UNION ALL
select cast(  case when no=1 then " else ‘union all’ end +char(13)+char(10) +’ select ‘+""+comm.fn_rand_str_between(1,10)+""–idn 身分證字號
+’,’+’N"‘+comm.fn_rand_str_between(1,10)+""–cust_name 客戶姓名
+’,’+""+comm.fn_rand_str_between(1,8)+""–birthday 生日
+’,’+""+comm.fn_rand_str_between(1,1)+""–gender 性別
+’,’+cast(comm.fn_rand(1,1000) as varchar)–height身高
+" as varchar(max)) as data
  from comm.fn_get_list(1,10)

修改產生的sql 語法改為正確的語法

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT ‘

INSERT INTO dbo.tb_customer with(tablock)
(idn,cust_name,birthday,gender,height)’ as data
UNION ALL
select cast(  case when row_number()over (order by getdate())=1 then " else ‘union all’ end +char(13)+char(10) +’ select ‘+""+idn+""–idn 身分證字號
+’,’+’N"‘+comm.fn_rand_str_between(6,10)+""–cust_name 客戶姓名
+’,’+""+cast(comm.fn_rand_date(‘19700101′,’20190830’) as varchar)+""–birthday 生日
+’,’+""+comm.fn_rand_str_in(‘F,M’)+""–gender 性別
+’,’+cast(comm.fn_rand(100,200) as varchar)–height身高
+" as varchar(max)) as data
  from [comm].[fn_rand_idn_list](10) –comm.fn_get_list(1,10)