在 SQL Server 中生成 INSERT 语句

• 下载源代码 - 1.18 KB

引言

存储过程 InsertGenerator 生成指定表名的 INSERT..VALUES 语句。

背景

SQL Server 不允许在选择“生成 SQL 脚本”选项时生成表数据的 INSERT 语句。解决方法是使用 DTS 在服务器之间传输数据。但是，存在一种需求，即从表中生成 INSERT 语句以移植数据。最简单的例子是当需要将少量或大量数据取出到可移动存储介质并在远程位置复制时，INSERT..VALUES 语句会派上用场。

使用代码

这个小巧但有用的存储过程将表名作为参数，并生成相同表的 INSERT SQL 语句。输出可以重定向到文本格式（Query Analyzer 中的 Ctrl+T）或输出到文本文件。该过程接受一个输入 varchar 类型参数，该参数必须是要生成语句的表名。

CREATE PROC InsertGenerator
(@tableName varchar(100)) as

然后，它包含一个游标，用于从 information_schema.columns 伪实体中获取特定于列的信息（列名及其数据类型），并循环遍历以构建 INSERT 和 VALUES 子句的 INSERT DML 语句。

--Declare a cursor to retrieve column specific information 
--for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR 
SELECT column_name,data_type FROM information_schema.columns 
    WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half 
                               --of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data 
                                   --(VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned 
                                 --for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
    begin
    print 'Table '+@tableName+' not found, processing skipped.'
    close curscol
    deallocate curscol
    return
END

WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
    SET @stringData=@stringData+'''''''''+
            isnull('+@colName+','''')+'''''',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype 
                                 --is text or something else 
BEGIN
    SET @stringData=@stringData+'''''''''+
          isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted 
                       --from varchar implicitly
BEGIN
    SET @stringData=@stringData+'''convert(money,''''''+
        isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE 
IF @dataType='datetime'
BEGIN
    SET @stringData=@stringData+'''convert(datetime,''''''+
        isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
END
ELSE 
IF @dataType='image' 
BEGIN
    SET @stringData=@stringData+'''''''''+
       isnull(cast(convert(varbinary,'+@colName+') 
       as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal 
BEGIN
    SET @stringData=@stringData+'''''''''+
          isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END

在构建了两个子句之后，VALUES 子句包含尾随逗号，需要将其替换为单引号。前缀子句只会删除尾随逗号。

DECLARE @Query nvarchar(4000) -- provide for the whole query, 
                              -- you may increase the size

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') 
    VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' 
    FROM '+@tableName
exec sp_executesql @query --load and run the built query

最终，关闭并释放为列信息创建的游标。

CLOSE cursCol
DEALLOCATE cursCol

编译并创建过程后，只需使用以下语法在 Query Analyzer 中运行它

InsertGenerator <tablename>

例如：

USE pubs
GO
InsertGenerator employee 
GO

然后复制 INSERT 语句并在查询分析器中运行它们。

在运行 INSERT 语句之前，应该传递 SET IDENTITY_INSERT <TABLENAME>ON 以将值添加到基于标识的列中。

关注点

D: T-SQL 爱好者可能希望扩展此过程以提供对二进制数据类型（如 IMAGE 等）的支持。

历史

• Ver 0.1b 添加于 2003 年 12 月 5 日。