最近闲的蛋疼,每每在写测试例子的时候,万恶的测试数据需要手工书写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 行受影响) */