生成表记录的SQL语句

 

生成表记录的SQL语句(注TEXT与NTEXT字段导出为NULL)

 

/*-------------------------------------------------------------------------------
功能:
     生成表记录的SQL语句(注TEXT与NTEXT字段导出为NULL)

参数说明:
     @Table_Name   表名
     @IsPrint         是否打印输入[1:是,0:否].
                     是:Print字符串在查询分析器中使用.
                     否:则为Select出表(默认为0:否)
    @IsMulti 是否生成多行语句
        =0 :  insert xx(...) values(..), (...),(...)
        =1 :  每行记录生成一条语句 insert xx(...) values(..)
   
--当表中有自增列[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 , @isMulti bit
set @Table_Name = 'Auth_Relevance'
set @IsPrint = 1
set @isMulti = 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 = case when @isMulti = 1 then '''' else '' end +  'insert into [' + @table_name + '] (' 
   SET @str_value   = case when @isMulti = 1 then  '''values ('' + ' else ''' ('' +' end 
 
   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) + ')  ' +  case when @isMulti = 1 then '''' else '' end 
   SELECT @str_value   = LEFT(@str_value, LEN(@str_value)-8) + ' + '') ''   '
 
   CREATE TABLE #returnTable (iden int identity(0,1), sqlString VARCHAR(MAX))
   DECLARE @sql VARCHAR(MAX)  
   if @isMulti = 1
   begin
        INSERT INTO #returnTable(sqlString) values('')
       INSERT INTO #returnTable(sqlString)
       EXEC ('select ' + @str_insert + ' +char(13)+ char(10)+' +  @str_value + '  +char(13)+ char(10)  from ' + @table_name + '  ')
   end
   else 
   begin   

        INSERT INTO #returnTable(sqlString) values( @str_insert + ' values')
       INSERT INTO #returnTable(sqlString)
       EXEC ('select '  +  @str_value + '  +char(13)+ char(10)  from ' + @table_name + '  ')
       update #returnTable set sqlString = ',' + sqlString where iden >1
   end

   IF @IsPrint = 0
       SELECT * FROM #returnTable
   ELSE
   BEGIN
     DECLARE @PrintString AS VARCHAR(MAX) , @iden int
     DECLARE print_cursor CURSOR FOR
     SELECT iden , sqlString
     FROM #returnTable 
     order by iden 

     OPEN print_cursor

     FETCH NEXT FROM print_cursor INTO @iden, @PrintString
     WHILE @@FETCH_STATUS = 0
     BEGIN     
       PRINT  @PrintString
       FETCH NEXT FROM print_cursor INTO @iden, @PrintString  
     END

     CLOSE print_cursor
     DEALLOCATE print_cursor
   END
   DROP TABLE #returnTable
   SET NOCOUNT OFF