把SQL Server数据表的内容转换为相应的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))+''''

END

FROM t1

 

现在我们着手考虑一个较为通用的方案，可以适用于各种不同的数据表。这意味着我们需要在sys.columns视图中获取各个列的名称和数据类型，用CASE函数对不同的数据类型作不同的处理，并用聚合赋值的方式把各个列的转换语句串联在一起，动态生成SQL语句，然后用EXEUTE关键字执行动态SQL语句。INSERT语句成为嵌套在动态SQL里面第二层动态SQL语句，字符串里面的每个单引号还得再次用两个单引号代替。实现该方案的脚本为：

DECLARE @table SYSNAME

SELECT @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 sys.columns

WHERE object_id=OBJECT_ID(@table)

EXEC('SELECT ' + @insert_sql + ' FROM ' + @table )

 

如果要支持二进制数据类型的话，需要编写一个标量函数，把二进制的值格式化为相应的字符串书写形式。好在SQL Server中有现成的，未公开的函数sys.fn_varbintohexstr就实现这样的格式化。SQL Server中还有一个更称心的未公开函数——sys.fn_sqlvarbasetostr，这个函数能够把各种常见数据类型的值格式化为相应的字符串书写方式，包括NULL。利用这个函数，上面的脚本可简化为：

DECLARE @table SYSNAME

SELECT @table = '替换为相应的表名'

DECLARE @insert_sql VARCHAR(MAX)

SELECT @insert_sql =

CASE

    WHEN @insert_sql IS NULL

       THEN '''INSERT INTO ' + @table + ' SELECT ''+'

    ELSE @insert_sql + '+'',''+'

END

+ 'sys.fn_sqlvarbasetostr(' + name + ')'

FROM sys.columns

WHERE object_id=OBJECT_ID(@table)

EXEC('SELECT ' + @insert_sql + ' FROM ' + @table)

 

sys.fn_sqlvarbasetostr函数接受sql_variant类型的参数。由于VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)、TEXT、NTEXT、IMAGE、XML、TIMESTAMP等类型不能转换为sql_variant，这意味着上面脚本不能处理使用这些类型的数据表。不过大值数据类型本来就不应该硬编码到SQL语句中。

读者可以进一步改进上面的脚本。比如，在INSERT语句中显式指定列名称，这样目标表中即使各列顺序不一致也可以正确导入数据。在某些场合，可能涉及数据库架构（SCHEMA），或者字段名比较特殊，必须得用中括号括起来。读者还可以考虑用UNION ALL把各行的数据生成的SELECT语句连接在一起，一次性插入所有行数据以提高执行效率。