转:一段有用的SQL代码

其功能是：将表中的数据生成SQL脚本，在查询分析器中执行这些脚本后自动将数据导入到SQL Server中

-- =============================================
-- Author:      佚名
-- Description: 将表中的数据转换成Insert语句的集
--              合的通用存储过程
-- =============================================
CREATE PROCEDURE  sp_ConvertDataToInsertSql
    @tablename   sysname   -- source table name
AS
BEGIN
    DECLARE   @column   varchar(1000)
    DECLARE   @columndata   varchar(1000)
    DECLARE   @sql   varchar(4000)
    DECLARE   @xtype   tinyint
    DECLARE   @name   sysname
    DECLARE   @objectId   int
    DECLARE   @objectname   sysname
    DECLARE   @ident   int

    SET NOCOUNT ON

    SET @objectId=object_id(@tablename)
    -- 判断对象是否存在
    IF @objectId IS NULL
    BEGIN
        print  @tablename +  '对象不存在'
        RETURN
    END

    SET @objectname=RTRIM(object_name(@objectId))
    IF @objectname IS NULL OR CHARINDEX(@objectname,@tablename) = 0
    BEGIN
        print  @tablename +  '对象不在当前数据库中'     
        RETURN
    END
    --判断对象是否是表
    IF OBJECTPROPERTY(@objectId,'IsTable') <> 1
    BEGIN
        print @tablename + '对象不是表'
        RETURN
    END

    SELECT @ident=status&0x80
      FROM syscolumns
     WHERE id=@objectid
       AND status&0x80=0x80
    IF @ident IS NOT NULL
        PRINT 'SET   IDENTITY_INSERT   '+ @TableName + '   ON'

    --定义游标，循环取数据并生成Insert语句
    DECLARE syscolumns_cursor CURSOR FOR
        SELECT c.name,c.xtype FROM syscolumns c
         WHERE c.id=@objectid
         ORDER BY c.colid
    --打开游标
    open syscolumns_cursor
    SET @column=''
    SET @columndata=''
    FETCH NEXT FROM syscolumns_cursor INTO @name, @xtype
    WHILE @@fetch_status <> -1
    BEGIN
        IF @@fetch_status <> -2
        BEGIN 
            --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理
            IF @xtype NOT IN(189,34,35,99,98)
                BEGIN
                SET @column=@column +
                    CASE WHEN len(@column)=0 THEN '' ELSE ',' END + @name
                SET @columndata = @columndata +
                    CASE WHEN len(@columndata)=0 THEN '' ELSE ','','',' END +
                    CASE WHEN @xtype IN(167,175) THEN '''''''''+' + @name+'+'''''''''   --varchar,char
                         WHEN @xtype IN(231,239) THEN '''N''''''+' + @name+'+'''''''''   --nvarchar,nchar
                         WHEN @xtype=61 THEN '''''''''+convert(char(23),' + @name+',121)+'''''''''   --datetime
                         WHEN @xtype=58 THEN '''''''''+convert(char(16),' + @name+',120)+'''''''''   --smalldatetime
                         WHEN @xtype=36 THEN '''''''''+convert(char(36),' + @name+')+'''''''''   --uniqueidentifier
                         ELSE @name
                END
            END
        END
        FETCH NEXT FROM syscolumns_cursor INTO @name,@xtype
    END
    -- 关闭游标
    CLOSE   syscolumns_cursor
    DEALLOCATE   syscolumns_cursor

    SET @sql = 'SET NOCOUNT ON SELECT ''INSERT '
        + @tablename + '(' + @column + ') VALUES(''as ''--'','
        + @columndata + ','')'' FROM ' + @tablename
    PRINT '--'+@sql

    EXEC(@sql)
    IF @ident IS NOT NULL
        PRINT 'SET IDENTITY_INSERT '+@TableName+' OFF'
END

 

调用存储过程：

 

EXEC sp_ConvertDataToInsertSql 'persons' 

 

得到的结果集类似下面的数据：

 

INSERT persons(username,lastname,firstname,city,id) VALUES('Bill','Bill','Zheng','Xuzhou',1)