Output to DDL SQL files

maxani

• February 01, 2011 00:00

• Answered

Follow

Hi.

Is there a way to output data to DDL SQL files (insert, update, delete)?

Indeed we can output to XML, XLS, CSV ... files but why not SQL files ?

Thanks for your advice.

• 

  avackova

  • February 01, 2011 13:09

  0

  Comment actions Permalink

  Hello Maxani,
  we don't have writer, that creates such output, but it can be easily achieved with Reformat component with transform attribute as follows:
  

  //#CTL1
  string query;
  record(Metadata4) input;
  // Transforms input record into output record.
  function transform() {
  	int i;
  	input = @0;
  	query = "insert into " + $table + " (";
  	for (i =0; i < length(input); i++) {
  		if (get_field_name(input,i) != "table") {
  			query = concat(query, get_field_name(input,i), ',');
  		}
  	}
  	query = substring(query,0,length(query) - 1);
  	query = query + ") values ('";
  	for (i =0; i < length(input); i++) {
  		if (get_field_name(input,i) != "table") {
  			if (get_field_type(input,i) == "string") {
  				query = concat(query, replace(nvl(input[i],''), "'", "\\\\'"), "','");
  			}else{
  				query = concat(query, input[i], "','");
  			}
  		}
  	}
  	query = substring(query,0,length(query) - 2);
  	query = concat(query, ')');
  	$query := query;
  }

Please sign in to leave a comment.