导出表数据的存储过程
创建一个导出表数据的存储过程
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