将表中数据变为insert语句

CREATE PROCEDURE [dbo].[Proc_GenerateInsert]
	@tablename SYSNAME,
	@filter NVARCHAR(500),
	@Order NVARCHAR(500)
AS
	DECLARE @column VARCHAR(MAX)   
	DECLARE @columndata VARCHAR(MAX)   
	DECLARE @sql VARCHAR(MAX)   
	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 'The object not exists' 
	    RETURN
	END
	
	SET @objectname = RTRIM(OBJECT_NAME(@objectId))   
	
	IF @objectname IS NULL
	   OR CHARINDEX(@objectname, @tablename) = 0 --此判断不严密
	BEGIN
	    PRINT 'object not in current database' 
	    RETURN
	END   
	
	IF OBJECTPROPERTY(@objectId, 'IsTable') < > 1 -- 判斷對象是否是table
	BEGIN
	    PRINT 'The object is not table' 
	    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'   
	
	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
	        IF @xtype NOT IN (189, 34, 35, 99, 98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理
	        BEGIN
	            SET @column = @column + CASE 
	                                         WHEN LEN(@column) = 0 THEN ''
	                                         ELSE ','
	                                    END + '[' + RTRIM(@name) +']'  
	            
	            SET @columndata = @columndata + CASE 
	                                                 WHEN LEN(@columndata) = 0 THEN 
	                                                      ''
	                                                 ELSE ','','','
	                                            END 
	                
	                + CASE 
	                       WHEN @xtype IN (167, 175) THEN '''''''''+rtrim([' + @name
	                            + '])+''''''''' --varchar,char
	                       WHEN @xtype IN (231, 239) THEN '''N''''''+rtrim([' + @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 '[' + RTRIM(@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 ''--'',' + RTRIM(@columndata) + ','')'' from ' + @tablename  
	
	IF @filter IS NOT NULL
	   AND LEN(RTRIM(@filter)) <> 0
	    SET @sql = @sql + ' where ' + @filter  
	
	IF @Order IS NOT NULL
	   AND LEN(RTRIM(@Order)) <> 0
	    SET @sql = @sql + ' Order By ' + @Order 
	
	PRINT '--' + @sql   
	EXEC (@sql)   
	
	IF @ident IS NOT NULL
	    PRINT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'  

注意： 在以文本输出时， select 很可能会只显示256个字符， 产生截断， 可以按上图设置即可。