导出表数据的存储过程

创建一个导出表数据的存储过程

CREATE   PROC [DBO].[SPGENINSERTSQL] (@TABLENAME VARCHAR(256))
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
DECLARE @SQLVALUES VARCHAR(8000)
SET @SQL =' ('
SET @SQLVALUES = 'VALUES (''+'
SELECT @SQLVALUES = @SQLVALUES + COLS + ' + '','' + ' ,@SQL = @SQL + '[' + NAME + '],'
  FROM
      (SELECT CASE
                WHEN XTYPE IN (48,52,56,59,60,62,104,106,108,122,127)       

                     THEN 'CASE WHEN '+ NAME +' IS NULL THEN ''NULL'' ELSE ' + 'CAST('+ NAME + ' AS VARCHAR)'+' END'

                WHEN XTYPE IN (58,61)

                     THEN 'CASE WHEN '+ NAME +' IS NULL THEN ''NULL'' ELSE '+''''''''' + ' + 'CAST('+ NAME +' AS VARCHAR)'+ '+'''''''''+' END'

               WHEN XTYPE IN (167)

                     THEN 'CASE WHEN '+ NAME +' IS NULL THEN ''NULL'' ELSE '+''''''''' + ' + 'REPLACE('+ NAME+','''''''','''''''''''')' + '+'''''''''+' END'

                WHEN XTYPE IN (231)

                     THEN 'CASE WHEN '+ NAME +' IS NULL THEN ''NULL'' ELSE '+'''N'''''' + ' + 'REPLACE('+ NAME+','''''''','''''''''''')' + '+'''''''''+' END'

                WHEN XTYPE IN (175)

                     THEN 'CASE WHEN '+ NAME +' IS NULL THEN ''NULL'' ELSE '+''''''''' + ' + 'CAST(REPLACE('+ NAME+','''''''','''''''''''') AS CHAR(' + CAST(LENGTH AS VARCHAR)  + '))+'''''''''+' END'

                WHEN XTYPE IN (239)

                     THEN 'CASE WHEN '+ NAME +' IS NULL THEN ''NULL'' ELSE '+'''N'''''' + ' + 'CAST(REPLACE('+ NAME+','''''''','''''''''''') AS CHAR(' + CAST(LENGTH AS VARCHAR)  + '))+'''''''''+' END'

                ELSE '''NULL'''

              END AS COLS,NAME

         FROM SYSCOLUMNS 

        WHERE ID = OBJECT_ID(@TABLENAME)

      ) T
SET @SQL ='SELECT ''INSERT INTO ['+ @TABLENAME + ']' + LEFT(@SQL,LEN(@SQL)-1)+') ' + LEFT(@SQLVALUES,LEN(@SQLVALUES)-4) + ')'' FROM '+@TABLENAME
PRINT @SQL
EXEC (@SQL)
END

 

转载于:https://www.cnblogs.com/liwenyan/archive/2012/06/08/2541758.html