create sql statement using unload statement

[Baron]

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:

1. Problem1: how I can get NULL instead of '' as tel for the second insert line?
2. Problem2: my actual table has too much columns, should I care about each column type whether numeric or char (for adding or not adding the single quotes)?

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;

[Breck_Carter]

> is there a simpler solution

No... the string concatenation operator is going to change a NULL operand into '', which is what most folks want.

It will not change a NULL operand into the 'NULL' string value without specific instructions ( if tel is NULL etcetera )

Note that ISQL often displays "NULL", but ISQL is a client application, it is not SQL Anywhere.

( be thankful that a single NULL operand in a string concatenation operation doesn't change the entire result value to a NULL value, which is what Null Purists expect 🙂

[VolkerBarth]

Therefore it might be easier to use the OUTPUT statement with a fitting value for the output_nulls option instead. Of course that would prevent its use within a SQL procedure or code block but you could use it within a dbisql batch.

---

FWIW, there may be alternative approaches to "unload data" that do not require INSERT statements, or you could have them generated automatically via DBISQL or SQL Remote...

Here's a sample line from the DBISQL's "Generate INSERT statement" context menu run over the contactzs table in the demo db - as you can see, numbers are not quoted, and NULLs are displayed as such:

INSERT INTO "GROUPO"."Contacts" ("ID","Surname","GivenName","Title","Street","City","State","Country","PostalCode","Phone","Fax","CustomerID") VALUES(5,'Sullivan','Dorothy','cs','541 Minuteman Dr.','Uxbridge','ME','USA','01742','5085553925','5085559931',NULL);

[Baron]

how can I generate the above sample line in dbisql or in dbremote?

[VolkerBarth]

Within DBISQL's results pane, you can select one, more or all rows and use the context menu to generate statements, and one of the choices is an INSERT statement. I have used it with one row of the Contacts table, so I typed SELECT * FROM Contacts, selected that row and used the right mouse for the context menu.

[Baron · Accepted solution]

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;

[fvestjens]

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 employees
-> based on demo database

This might also work