Querying two tables simultaneously and inserting the data from one into another

[hervinder]

Hi all,

I am querying two table and i am able to insert the data from one into another but the problem i am facing is that-

on update command it is not updating the table rather it is inserting the records again.

var a = new GlideRecord('u_source_table');

a.query();

while(a.next())

{

  var b = new GlideRecord('u_target_table');

  b.addQuery("u_name", a.u_name);

  if(b.next())

  {

  b.u_name=a.u_name;

b.u_age=a.u_age;

  gs.log("inside update");

b.update();

  }

  else{

  gs.log("inside insert");

  b.initialize();

        b.u_name=a.u_name;

b.u_age=a.u_age;

  b.insert();

  }

}

Please provide your valuable comments.

[Greg42]

Hi there,

I think the problem is that you don't query your b table and your if statement fails at all times falling to else in each call.

var a = new GlideRecord('u_source_table');
a.query();

while(a.next()){
         var b = new GlideRecord('u_target_table');
         b.addQuery("u_name", a.u_name);
         b.query(); <-- query the table

         if(b.next()){
                   b.u_name=a.u_name;
                   b.u_age=a.u_age;
                   gs.log("inside update");
                   b.update();
         } else {
                   gs.log("inside insert");
                   b.initialize();
                   b.u_name=a.u_name;
                   b.u_age=a.u_age;
                   b.insert();
         }
}

Cheers

Greg

[hervinder]

Hi Greg,

I have tried using query statement too but its still not working.

[Greg42 · Accepted solution]

Hi Hervinder,

This is very odd. I have just tested this code and it works fine.

I have:

- created 3 users in source table

- left target table empty

- run the code resulted in 3 new records in target table

- run the code again resulted in no new records

- changed surname in one record and name in the other (source table) resulted in those records being updated and no new records created

- added a new record to source table and run the code

- new record showed in target table

I did different combination of insert update before running the code and all seems fine.

What I would do first is go through simple stuff - as it is sometime very easy to miss - and check both tables set up like field names/type, query the source table and see what you get back. Query the second table with those results first and see if they are picked up by it. Manually check if you get results you expect. By manually I mean have two same records in both tables, query target table with source table result and see if it's picked up and try to update it.

Cheers

Greg

[hervinder]

Hi Greg,

Please advice on Need to change approval summarizer for checkbox type variables