on 2022 Jul 18 11:28 AM
I need to unload the contents of a table into a sql file (as sql statements).
I tried the following:
create or replace table employees (empname varchar(100), tel varchar(20) null, salary double); insert into employees values ('emp1', '0660584578', 100), ('emp1', null, 100) unload select 'insert into employees values (''' || empname || ''', ''' || tel || ''',' || salary || ');' from employees to 'c:\\temp\\employees.txt' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
As a result I get 2 lines in employees.txt:
insert into employees values ('emp1', '0660584578',100);
insert into employees values ('emp1', '',100);
Hier I have two problems:
For this particular example I can solve Problem1 using if/endif, but is there a simpler solution?
unload select 'insert into employees values (''' || empname || ''', ' || (if tel is null then 'null' else '''' || tel || '''' endif)|| ',' || salary || ');' from employees to 'c:\\temp\\employees.txt' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
thank you for the help, I built my own procedure which does exactly what I want (I could not explain all what I need in my first question).
create or replace procedure CreateInsertStatement( in TableName varchar(255), in WhereStmt varchar(512), in FileName varchar(1024), in TriggerOff bit default 0, in ConflictSkipUpdate varchar (40) default null, in Orderby varchar (40) default null, in AppendOrNot bit default 1 ) begin declare @sql1 long varchar; declare @sql2 long varchar; declare @sql3 long varchar; declare @orderby long varchar; declare @OnExist varchar(40); set @OnExist = ''; if ConflictSkipUpdate like '%update%' then set @OnExist = ' on existing update '; end if; if ConflictSkipUpdate like '%skip%' then set @OnExist = ' on existing skip '; end if; if isnull(Orderby, '') = '' then set @orderby = ' order by ' || (select list('"' || cname || '"' order by colno) from sys.syscolumns where tname = TableName and in_primary_key = 'Y'); end if; set @sql2 = (select list ( (if coltype like '%char%' or coltype like '%time%' or coltype like '%date%' then '(if "' || cname || '" is null then ''null '' else '''''''' || replace("' || cname || '",'''''''','''''''''''') || '''''''' endif)' else '(if "' || cname || '" is null then ''null '' else "' || cname || '" || '''' endif)' endif), '||'',''||\\x0d\ ' ) from sys.syscolumns where tname = TableName); if isnull(TriggerOff, '1') <> '0' then set @sql1 = 'unload select ''set temporary option Fire_Triggers = "OFF";'' '||' to ''' || FileName || ''' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF'; else set @sql1 = 'unload select ''--set temporary option Fire_Triggers = "OFF";'' '||' to ''' || FileName || ''' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF'; end if; set @sql2 = 'unload select ''insert into ' || TableName || @OnExist || ' values ('' ||\\x0d\ ' || @sql2; set @sql2 = @sql2 ||'\\x0d\ || '');'' from ' || TableName || ' ' || WhereStmt || ' ' || @orderby; if isnull(TriggerOff, '1') = '1' then set @sql3 = 'unload select ''set temporary option Fire_Triggers = "ON";'' '||' to ''' || FileName || ''' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF APPEND ON;\\x0d\ '; else set @sql3 = 'unload select ''--set temporary option Fire_Triggers = "ON";'' '||' to ''' || FileName || ''' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF APPEND ON;\\x0d\ '; end if; set @sql2 = @sql2 ||' to ''' || FileName || ''' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF APPEND ON;' || '\\x0d\ '; if isnull(AppendOrNot, 1) = 1 then set @sql1 = @sql1 || ' APPEND ON;\\x0d\ '; else set @sql1 = @sql1 || ' APPEND OFF;\\x0d\ '; end if; select @sql1 || @sql2 || @sql3; end;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You could create the procedure below:
create or replace function ConvertValue(in in_Value long varchar) returns long varchar begin if in_Value is null then return 'null' end if; -- if IsDate(in_Value) = 1 or IsNumeric(in_Value) = 1 then return in_Value end if; -- return ''''|| in_Value ||''''; end;Then your statement could look like:
select 'insert into Employees values('|| ConvertValue(EmployeeID) ||','|| ConvertValue(Phone) ||','|| ConvertValue(Street) ||','|| ConvertValue(StartDate) ||','|| ConvertValue(TerminationDate) ||');' from employeesThis might also work-> based on demo database
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
69 | |
9 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.