生成表记录的SQL语句(注TEXT与NTEXT字段导出为NULL)
/*------------------------------------------------------------------------------- 功能: 生成表记录的SQL语句(注TEXT与NTEXT字段导出为NULL) 参数说明: @Table_Name 表名 @IsPrint 是否打印输入[1:是,0:否]. 是:Print字符串在查询分析器中使用. 否:则为Select出表(默认为0:否) --当表中有自增列[identity]时,将生成的SQL语句放入下方 Insert Sql语句 处 ,执行 set identity_insert [thetablename] on go ..Insert Sql语句 go set identity_insert [thetablename] off go -------------------------------------------------------------------------------------*/ declare @Table_Name varchar(500),@IsPrint bit set @Table_Name = 'erp_sys_menus' set @IsPrint = 1 SET NOCOUNT ON DECLARE @obj_name AS SYSNAME DECLARE @column_name AS SYSNAME DECLARE @usr_defined_dtype AS SYSNAME DECLARE @sys_dtype AS SYSNAME DECLARE @str_insert AS VARCHAR(MAX) DECLARE @str_value AS VARCHAR(MAX) DECLARE @cu_obj CURSOR SET @cu_obj = CURSOR LOCAL SCROLL FOR SELECT sobj.name AS obj_name, scol.name AS column_name, styp.name AS usr_defined_dtype, styp1.name AS sys_dtype FROM sysobjects sobj INNER JOIN syscolumns scol ON scol.id = sobj.id INNER JOIN systypes styp ON styp.xtype = scol.xtype AND styp.xusertype = scol.xusertype INNER JOIN systypes styp1 ON styp1.xtype = styp.xtype AND styp1.xusertype = styp.xtype WHERE sobj.xtype = 'U' AND sobj.name = @Table_Name ORDER BY scol.colid SET @str_insert = '''insert into [' + @table_name + '] (' SET @str_value = '''values ('' + ' OPEN @cu_obj FETCH NEXT FROM @cu_obj INTO @obj_name, @column_name, @usr_defined_dtype, @sys_dtype WHILE @@FETCH_STATUS = 0 BEGIN IF @sys_dtype <> 'image' BEGIN SET @str_insert = @str_insert +'['+ @column_name + '],' BEGIN SET @str_value = @str_value + 'case when ' + @column_name + ' is null then ''null'' else ' IF @sys_dtype IN ('char', 'varchar', 'nchar', 'nvarchar') BEGIN SET @str_value = @str_value + ''''''''' + ' + 'replace(' + @column_name + ', '''''''', '''''''''''')' + ' + ''''''''' END ELSE IF @sys_dtype IN ('text','ntext') BEGIN SET @str_value = @str_value + '''null''' END ELSE IF @sys_dtype IN ('datetime','datetime2', 'smalldatetime','date') BEGIN SET @str_value = @str_value + ''''''''' + ' + 'convert(varchar, ' + @column_name + ',120)' + ' + ''''''''' END ELSE IF @sys_dtype IN ('bigint', 'int', 'smallint', 'tinyint', 'bit', 'decimal', 'numeric', 'money', 'smallmoney', 'float', 'real') BEGIN SET @str_value = @str_value + 'convert(varchar, ' + @column_name + ')'+ ' ' END ELSE BEGIN SET @str_value = @str_value + ''''''''' + ' + @column_name + ' + ''''''''' END SET @str_value = @str_value + ' end ' END SET @str_value = @str_value + '+ '', '' + ' END FETCH NEXT FROM @cu_obj INTO @obj_name, @column_name, @usr_defined_dtype, @sys_dtype END CLOSE @cu_obj SELECT @str_insert = LEFT(@str_insert, LEN(@str_insert)-1) + ') '' ' SELECT @str_value = LEFT(@str_value, LEN(@str_value)-8) + ' + '') '' ' CREATE TABLE #returnTable (sqlString VARCHAR(MAX)) DECLARE @sql VARCHAR(MAX) INSERT INTO #returnTable EXEC ('select ' + @str_insert + ' + char(10)+' + @str_value + ' + char(10) from ' + @table_name + ' ') IF @IsPrint = 0 SELECT * FROM #returnTable ELSE BEGIN DECLARE @PrintString AS VARCHAR(MAX) DECLARE print_cursor CURSOR FOR SELECT sqlString FROM #returnTable OPEN print_cursor FETCH NEXT FROM print_cursor INTO @PrintString WHILE @@FETCH_STATUS = 0 BEGIN PRINT @PrintString FETCH NEXT FROM print_cursor INTO @PrintString END CLOSE print_cursor DEALLOCATE print_cursor END DROP TABLE #returnTable SET NOCOUNT OFF