sql server根据表中数据生成insert语句

 sql server根据表中数据生成insert语句

-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程，执行spGenInsertSQL 表名-- ======================================================
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

-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程，执行proc_insert 表名-- ======================================================
CREATE proc [dbo].[proc_insert] (@tablename varchar(256))
as
begin
       set nocount on
       --declare @tablename varchar(256)
       --set @tablename = 'AD'
       declare @sqlstr varchar(4000)
       declare @sqlstr1 varchar(4000)
       declare @sqlstr2 varchar(4000)
       select @sqlstr='select ''insert '+@tablename
 
       select @sqlstr1=''
       select @sqlstr2=' ('
       select @sqlstr1= ' values ( ''+'
       select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+ '[' + name + ']' +',' from (select case 
--     when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
       when a.xtype =104 then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar(1),['+a.name +'])'+' end'
       when a.xtype =175 then 'case when ['+a.name+'] is null then ''NULL'' else '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end'
       when a.xtype =61  then 'case when ['+a.name+'] is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),['+a.name +'],121)'+ '+'''''''''+' end'
       when a.xtype =106 then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),['+a.name +'])'+' end'
       when a.xtype =62  then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar(23),['+a.name +'],2)'+' end'
       when a.xtype =56  then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar(11),['+a.name +'])'+' end'
       when a.xtype =60  then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar(22),['+a.name +'])'+' end'
       when a.xtype =239 then 'case when ['+a.name+'] is null then ''NULL'' else '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end'
       when a.xtype =108 then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),['+a.name +'])'+' end'
       when a.xtype =231 then 'case when ['+a.name+'] is null then ''NULL'' else '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end'
       when a.xtype =59  then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar(23),['+a.name +'],2)'+' end'
       when a.xtype =58  then 'case when ['+a.name+'] is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),['+a.name +'],121)'+ '+'''''''''+' end'
       when a.xtype =52  then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar(12),['+a.name +'])'+' end'
       when a.xtype =122 then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar(22),['+a.name +'])'+' end'
       when a.xtype =48  then 'case when ['+a.name+'] is null then ''NULL'' else '+'convert(varchar(6),['+a.name +'])'+' end'
--     when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
       when a.xtype =167 then 'case when ['+a.name+'] is null then ''NULL'' else '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end'
       else '''NULL'''
       end as col,a.colid,a.name
       from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and  a.xtype <>36
       )t order by colid
       select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
       --print @sqlstr
       exec( @sqlstr)
       set nocount off
end

摘自-http://www.cnblogs.com/stu-acer/archive/2010/10/24/1859803.html 

Oracle根据表中数据生成insert语句

简便型

    select 'insert into TableNameA values ('||''''||Column1||''''||','||''''||Column2||''''||','||''''||Column3||''''||')' From TableNameB

CREATE OR REPLACE PROCEDURE genins_file(
　　p_table　IN varchar2,
　　p_output_folder IN VARCHAR2,
　　p_output_file　 IN VARCHAR2)
　　IS
　　--
　　l_column_list　　VARCHAR2(32767);
　　l_value_list　　 VARCHAR2(32767);
　　l_query　　　　　VARCHAR2(32767);
　　l_cursor　　　　 NUMBER;
　　ignore　 NUMBER;
　　l_insertline1　　varchar2(32767);
　　l_insertline2　　varchar2(32767);
　　cmn_file_handle　　　 UTL_FILE.file_type;
　　--
　　FUNCTION get_cols(p_table VARCHAR2)
　　RETURN VARCHAR2
　　IS
　　l_cols VARCHAR2(32767);
　　CURSOR l_col_cur(c_table VARCHAR2) IS
　　SELECT column_name
　　FROM　 user_tab_columns
　　WHERE　table_name = upper(c_table)
　　ORDER BY column_id;
　　BEGIN
　　l_cols := null;
　　FOR rec IN l_col_cur(p_table)
　　LOOP
　　l_cols := l_cols || rec.column_name || ',';
　　END LOOP;
　　RETURN substr(l_cols,1,length(l_cols)-1);
　　END;
　　--
　　FUNCTION get_query(p_table IN VARCHAR2)
　　RETURN VARCHAR2
　　IS
　　l_query VARCHAR2(32767);
　　CURSOR l_query_cur(c_table VARCHAR2) IS
　　SELECT 'decode('||column_name||',null,''null'','||
　　decode(data_type,'VARCHAR2','''''''''||'||column_name ||'||'''''''''
　　,'DATE','''''''''||to_char('||column_name||',''YYYY-MM-DD HH24:MI:SS'')||'''''''''
　　,column_name
　　) || ')' column_query
　　FROM user_tab_columns
　　WHERE table_name = upper(c_table)
　　ORDER BY column_id;
　　BEGIN
　　l_query := 'SELECT ';
　　FOR rec IN l_query_cur(p_table)
　　LOOP
　　l_query := l_query || rec.column_query || '||'',''||';
　　END LOOP;
　　l_query := substr(l_query,1,length(l_query)-7);
　　RETURN l_query || ' FROM ' || p_table;
　　END;
　　--
　　BEGIN
　　l_column_list　:= get_cols(p_table);
　　l_query　　　　:= get_query(p_table);
　　l_cursor := dbms_sql.open_cursor;
　　DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native);
　　DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list, 32767);
　　ignore := DBMS_SQL.EXECUTE(l_cursor);
　　--
　　IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
　　cmn_file_handle := UTL_FILE.FOPEN (p_output_folder, p_output_file, 'a',32767);
　　END IF;
　　
　　LOOP
　　IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
　　DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list);
　　l_insertline1:='INSERT INTO '||p_table||' ('||l_column_list||')';
　　l_insertline2:=' VALUES ('||l_value_list||');';
　　UTL_FILE.put_line (cmn_file_handle, l_insertline1);
　　UTL_FILE.put_line (cmn_file_handle, l_insertline2);
　　ELSE
　　EXIT;
　　END IF;
　　END LOOP;
　　IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
　　UTL_FILE.FCLOSE (cmn_file_handle);
　　END IF;
　　END;

调用它，可以把表里的数据生成(insert into 表名 ....)OS下文件的过程genins_file方法:
　　
　　　　　　SQL>exec genins_file('emp','/oracle/logs','insert_emp.sql');
　　　　　　　　　　　　　　　　　 |　　　　 |　　　　　　　　|
　　　　　　　　　　　　　　　 表名,可变　　 |　　　　 生成OS下文件名,可变
　　　　　　　　　　　　　　　　　　　　　　 |
　　　　　　　　　　　　　　　　　　utl_file_dir路径名,不变(我设置的是/oracle/logs)
　　
　　　　　　SQL> exit
　　
　　可以在OS目录/oracle/logs下看到insert_emp.sql文件。

调用它之前，要看看数据库的初始化参数 UTL_FILE_DIR 是否已经正确地设置:
　　
　　SQL> show parameters utl_file_dir;
　　
　　可以看到该参数的当前设置。
　　
　　如果没有值，必须修改数据库的initsid.ora文件，将utl_file_dir 指向一个你想用PL/SQL file I/O 的路径。
　　
　　重新启动数据库。此参数才生效。