格式化测试数据，组装用于插入表中的sql语句
最近闲的蛋疼，每每在写测试例子的时候，万恶的测试数据需要手工书写insert语句的向表中插入，很费事，于是就像写个脚本来实现自动生成插入语句
测试数据：
100 北京 20120203123
100 天津20120203123
101 湖南20120203null
101 海南20120203123
102 上海20120203123
102 云南20120203123
需要把这些数据插入到某张表中，如果您使用书写insert into（......）values（，，，，）需要用逗号隔开，还需要加单引号，确实挺麻烦的于是乎就写了个脚本，根据表空间的每个字段类型自动组装插入sql语句，下面采用的是insert into tb select a,b,c union all select xxxx的方式
我的思路是列与列之间用逗号隔开，行与行之间用分号隔开（呵呵，其实这样的话其实也省不了多少时间）
采用了2层while循环，先循环每一行，对每一行每一列的数据进行判断数据类型，是否需要加单引号，这个可以通过操作字符串的一些函数来实现
对已经循环过的数据进行剔除，继续对剩余的字符串继续循环拼装sql
写完之后突然感觉写的好麻烦，就一个拼装进行插入的sql语句，至于写的那么麻烦吗，但是没有别的思路了，抛砖引玉，希望哪位大神指点一下
脚本如下：
/*
=====================
Author: lordbaby
Datetime: 2012-5-18
Description: convert test data to insert sql
=====================
*/
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
CREATE TABLE TB(col1 INT,col2 VARCHAR(20),col3 datetime,col4 numeric(12,2))
DECLARE @testData NVARCHAR(max)
DECLARE @sqlStr NVARCHAR(max)
DECLARE @rowCount INT
DECLARE @colCount INT
DECLARE @i INT
DECLARE @j INT
DECLARE @data_type VARCHAR(10)
DECLARE @rowData VARCHAR(100)
DECLARE @colData VARCHAR(100)
SELECT @sqlStr='select '
---稍微加工一下测试数据
--列与列之间用逗号隔开，行与行之间用分号隔开
SELECT @testData=
'100 , 北京,20120203,123;
100 ,天津,20120203,123;
101 ,湖南,20120203,null;
101 ,海南,20120203,123;
102 , 上海,20120203,123;
102 ,云南,20120203,123;'
--请输入需要几行测试数据
SELECT @rowCount=6
--需要几列测试数据
SELECT @colCount= count(1) from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tb'
SELECT @i=1
SELECT @j=1
WHILE @i<=@rowCount
BEGIN
SELECT @rowData=''
--截取每一行的数据
IF CHARINDEX(';',@testData)>0
BEGIN
SELECT @rowData=@rowData+LTRIM(SUBSTRING(@testData,1,CHARINDEX(';',@testData)-1))
SELECT @testData=LTRIM(SUBSTRING(@testData,CHARINDEX(';',@testData)+1,LEN(@testData)-LEN(@rowData)+1))
--PRINT @testData
END
ELSE
BEGIN
BREAK
END
SELECT @j=1
WHILE @j<=@colCount
BEGIN
SELECT @colData=''
SELECT @data_type=DATA_TYPE
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num,COLUMN_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tb'
) AS t WHERE num=@j
IF (@data_type='int' OR @data_type='numeric' OR @data_type='decimal')
BEGIN
--截取某行每一列的值
IF CHARINDEX(',',@rowData)>0
BEGIN
SELECT @colData=SUBSTRING(@rowData,1,CHARINDEX(',',@rowData)-1)
SELECT @sqlStr=@sqlStr+' '+@colData+' '+','
SELECT @rowData=LTRIM(SUBSTRING(@rowData,CHARINDEX(',',@rowData)+1,LEN(@rowData)-LEN(@colData)+1))
END
ELSE
BEGIN
SELECT @colData=@rowData
SELECT @sqlStr=@sqlStr+' '+@colData+' union all select'
END
END
IF (@data_type='varchar' OR @data_type='nvarchar' OR @data_type='datetime')
BEGIN
IF CHARINDEX(',',@rowData)>0
BEGIN
SELECT @colData=SUBSTRING(@rowData,1,CHARINDEX(',',@rowData)-1)
SELECT @sqlStr=@sqlStr+CASE WHEN UPPER(LTRIM(RTRIM(@colData)))='NULL' THEN 'null'+','
ELSE ' '+''''+@colData+''''+' '+',' END
SELECT @rowData=LTRIM(SUBSTRING(@rowData,CHARINDEX(',',@rowData)+1,LEN(@rowData)-LEN(@colData)+1))
END
ELSE
BEGIN
SELECT @colData=@rowData
SELECT @sqlStr=@sqlStr+CASE WHEN UPPER(LTRIM(RTRIM(@colData)))='NULL' THEN 'null'
ELSE ' '+''''+@colData+''''+' ' END
+' union all select '
END
END
SELECT @j=@j+1
END
SELECT @i=@i+1
END
PRINT left(@sqlStr,len(@sqlStr)-len('union all select'))
--这里采用的是 inser into tb select xxxx union all select cxxxx....
/*
insert into tb
select 100 , '北京' , '20120203' , 123 union all select
100 , '天津' , '20120203' , 123 union all select
101 , '湖南' , '20120203' , 123 union all select
101 , '海南' , '20120203' , 123 union all select
102 , '上海' , '20120203' , 123 union all select
102 , '云南' , '20120203' , 123
*/
select * from tb
/*
col1 col2 col3 col4
----------- -------------------- ----------------------- --------------
100 北京 2012-02-03 00:00:00.000 123.00
100 天津 2012-02-03 00:00:00.000 123.00
101 湖南 2012-02-03 00:00:00.000 123.00
101 海南 2012-02-03 00:00:00.000 123.00
102 上海 2012-02-03 00:00:00.000 123.00
102 云南 2012-02-03 00:00:00.000 123.00
(6 行受影响)
*/
