sql server根据表中数据生成insert语句
-- ======================================================--根据表中数据生成insert语句的存储过程--建立存储过程，执行spGenInsertSQL 表名-- ======================================================CREATE proc [dbo].[spGenInsertSQL] (@tablename varchar(256))asbegindeclare @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,namefrom 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 @sqlexec (@sql)end-- ======================================================--根据表中数据生成insert语句的存储过程--建立存储过程，执行proc_insert 表名-- ======================================================CREATE proc [dbo].[proc_insert] (@tablename varchar(256))asbeginset 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 '+@tablenameselect @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.namefrom 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 colidselect @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename--print @sqlstrexec( @sqlstr)set nocount offend
摘自-/stu-acer/archive//10/24/1859803.html
Oracle根据表中数据生成insert语句
简便型
select 'insert into TableNameA values ('||''''||Column1||''''||','||''''||Column2||''''||','||''''||Column3||''''||')' From TableNameB
CREATE OR REPLACE PROCEDURE genins_file(p_tableIN varchar2,p_output_folder IN VARCHAR2,p_output_file IN VARCHAR2)IS--l_column_listVARCHAR2(32767);l_value_list VARCHAR2(32767);l_queryVARCHAR2(32767);l_cursor NUMBER;ignore NUMBER;l_insertline1varchar2(32767);l_insertline2varchar2(32767);cmn_file_handle UTL_FILE.file_type;--FUNCTION get_cols(p_table VARCHAR2)RETURN VARCHAR2ISl_cols VARCHAR2(32767);CURSOR l_col_cur(c_table VARCHAR2) ISSELECT column_nameFROM user_tab_columnsWHEREtable_name = upper(c_table)ORDER BY column_id;BEGINl_cols := null;FOR rec IN l_col_cur(p_table)LOOPl_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 VARCHAR2ISl_query VARCHAR2(32767);CURSOR l_query_cur(c_table VARCHAR2) ISSELECT '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_queryFROM user_tab_columnsWHERE table_name = upper(c_table)ORDER BY column_id;BEGINl_query := 'SELECT ';FOR rec IN l_query_cur(p_table)LOOPl_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;--BEGINl_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) THENcmn_file_handle := UTL_FILE.FOPEN (p_output_folder, p_output_file, 'a',32767);END IF;LOOPIF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THENDBMS_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);ELSEEXIT;END IF;END LOOP;IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THENUTL_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 的路径。
重新启动数据库。此参数才生效。
