sql server根据表中数据生成insert语句
-- ======================================================--根据表中数据生成insert语句的存储过程--建立存储过程，执行spGenInsertSQL 表名-- ======================================================CREATE proc [dbo].[spGenInsertSQL] (@tablename varchar(256))asbegin 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))asbegin 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 offend 摘自-
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 的路径。 重新启动数据库。此参数才生效。