|
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\x0a' )
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\x0a' || @sql2;
set @sql2 = @sql2 ||'\x0d\x0a || '');'' 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\x0a';
else
set @sql3 = 'unload select ''--set temporary option Fire_Triggers = "ON";'' '||' to ''' || FileName || ''' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF APPEND ON;\x0d\x0a';
end if;
set @sql2 = @sql2 ||' to ''' || FileName || ''' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF APPEND ON;' || '\x0d\x0a';
if isnull(AppendOrNot, 1) = 1 then
set @sql1 = @sql1 || ' APPEND ON;\x0d\x0a';
else
set @sql1 = @sql1 || ' APPEND OFF;\x0d\x0a';
end if;
select @sql1 || @sql2 || @sql3;
end;
|
|
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
This might also work
|
> 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 :)
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);how can I generate the above sample line in dbisql or in dbremote?
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.