2023年7月13日发(作者：)

把SQLServer数据表的内容转换为相应的INSERT语句笔者曾在《程序员》2009年11期上探讨Transact-SQL的元编程，即通过⽬录视图、元数据函数等⽅式访问数据库的元数据信息，在执⾏过程中动态⽣成SQL脚本。当时限于篇幅，所给的例⼦较少。这⾥给出动态⽣成SQL脚本的⼀个典型应⽤，把数据表的内容转换为相应的INSERT语句。这个启发来⾃我管理远程数据库的经历。我常常需要⽤本地SQL Server数据库中的⼀个表的内容，去更新远程数据库中同名表中的内容。表中的内容只有数⼗⾏。⽹管屏蔽了数据库的1433端⼝，我只能使⽤远程桌⾯登录上去访问数据库。远程桌⾯⽀持剪贴板复制粘贴，也⽀持⽂件传输，剪贴板对于传输少量的⽂本数据很⽅便，⽂件传输要⿇烦些且不太安全。我希望能把本机从表中查询出来的内容转换为INSERT语句，这样的话，就可以⽅便地复制到远程机器上执⾏。由于⽣成的INSERT语句既取决于表的结构，也取决于表中的数据，⽣成这样的脚本是⽐较⿇烦的。按照循序渐进的原则，我们先考虑简单的情况，假定数据表的结构是已知的。这⾥虚构了⼀个表，包含了⼏种代表性数据类型，但不含⼆进制数据。下⾯是表的定义脚本：CREATE TABLE t1( c1 INT, c2 VARCHAR(10), c3 DATETIME)

我们依次看各个列在INSERT语句中是怎么表⽰的。整数列不需要任何修饰，但由于动态⽣成的SQL语句是⽂本，列的值需⽤CAST或CONVERT函数转换为字符串。字符串列需要⽤单引号括起来。注意每个单引号在字符串中需⽤两个单引号表⽰。⽇期类型既需要转换，⼜得⽤单引号括起来，这⾥⽇期类型显⽰的格式并不重要。这样，⽣成的INSERT语句的脚本应该像下⾯这个样⼦：SELECT 'INSERT INTO t1 SELECT '+ CAST(c1 AS VARCHAR(100)) +','+ ''''+c2+'''' +','+ ''''+CAST(c3 AS VARCHAR(100))+''''FROM t1

上⾯的脚本忽略了⼀个特殊但很常见的值，就是NULL。不管列本来的数据类型是什么，值为NULL时在INSERT语句中总是⽤字符串NULL表⽰，不加引号。我们可以⽤CASE函数处理值为NULL的情况。这样，上⾯的脚本改进为：SELECT 'INSERT INTO t1 SELECT ' +CASE WHEN c1 IS NULL THEN 'NULL' ELSE CAST(c1 AS VARCHAR(100))END +',' +CASE WHEN c2 IS NULL THEN 'NULL' ELSE ''''+c2+''''END +',' +CASE WHEN c3 IS NULL THEN 'NULL' ELSE ''''+CAST(c3 AS VARCHAR(100))+''''ENDFROM t1

现在我们着⼿考虑⼀个较为通⽤的⽅案，可以适⽤于各种不同的数据表。这意味着我们需要在s视图中获取各个列的名称和数据类型，⽤CASE函数对不同的数据类型作不同的处理，并⽤聚合赋值的⽅式把各个列的转换语句串联在⼀起，动态⽣成SQL语句，然后⽤EXEUTE关键字执⾏动态SQL语句。INSERT语句成为嵌套在动态SQL⾥⾯第⼆层动态SQL语句，字符串⾥⾯的每个单引号还得再次⽤两个单引号代替。实现该⽅案的脚本为：DECLARE @table SYSNAMESELECT @table = '替换为相应的表名'DECLARE @insert_sql VARCHAR(MAX)SELECT @insert_sql =CASE WHEN @insert_sql IS NULL THEN '''INSERT INTO ' + @table + ' SELECT ''+' ELSE @insert_sql + '+'',''+'END+ 'CASE WHEN ' + name + ' IS NULL THEN ''NULL'' ELSE ' +CASE WHEN RIGHT(TYPE_NAME(system_type_id),4) IN ('CHAR','TEXT') THEN '''''''''+' + name + '+''''''''' WHEN TYPE_NAME(system_type_id) IN ('DATETIME','UNIQUEIDENTIFIER') THEN '''''''''+CAST(' + name + ' AS VARCHAR(100))+''''''''' ELSE 'CAST(' + name + ' AS VARCHAR(100))'END+' END'FROM sWHERE object_id=OBJECT_ID(@table)EXEC('SELECT ' + @insert_sql + ' FROM ' + @table )

如果要⽀持⼆进制数据类型的话，需要编写⼀个标量函数，把⼆进制的值格式化为相应的字符串书写形式。好在SQL Server中有现成的，未公开的函数_varbintohexstr就实现这样的格式化。SQL Server中还有⼀个更称⼼的未公开函数——_sqlvarbasetostr，这个函数能够把各种常见数据类型的值格式化为相应的字符串书写⽅式，包括NULL。利⽤这个函数，上⾯的脚本可简化为：DECLARE @table SYSNAMESELECT @table = '替换为相应的表名'DECLARE @insert_sql VARCHAR(MAX)SELECT @insert_sql =CASE WHEN @insert_sql IS NULL THEN '''INSERT INTO ' + @table + ' SELECT ''+' ELSE @insert_sql + '+'',''+'END+ '_sqlvarbasetostr(' + name + ')'FROM sWHERE object_id=OBJECT_ID(@table)EXEC('SELECT ' + @insert_sql + ' FROM ' + @table)

_sqlvarbasetostr函数接受sql_variant类型的参数。由于VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)、TEXT、NTEXT、IMAGE、XML、TIMESTAMP等类型不能转换为sql_variant，这意味着上⾯脚本不能处理使⽤这些类型的数据表。不过⼤值数据类型本来就不应该硬编码到SQL语句中。读者可以进⼀步改进上⾯的脚本。⽐如，在INSERT语句中显式指定列名称，这样⽬标表中即使各列顺序不⼀致也可以正确导⼊数据。在某些场合，可能涉及数据库架构（SCHEMA），或者字段名⽐较特殊，必须得⽤中括号括起来。读者还可以考虑⽤UNION ALL把各⾏的数据⽣成的SELECT语句连接在⼀起，⼀次性插⼊所有⾏数据以提⾼执⾏效率。