How to append legacy data table with new data from database script

[#1]

Hi all,

I have a static JMP data table that contains old, historical information (legacy data).

I have another JMP data table that contains new data. This table is fed from a database through a JMP database query. That also means JMP automatically provides me with a nice script that allows to update the table from the database each time I run it.

Now I want to have the old table appended with the new data that is being pulled from the database, obviously without losing the old data. Probably JSL is needed for this, but I can't figure it out myself.

Any help gladly appreciated,

Many thanks

Guy

[#2 · Accepted solution]

Hi,

I have another solution I think might work.  The concatenate function is back to appending to the first table (dt1), but now creates a source column to keep track of which rows were in dt1 and which were appended from dt2.  "Onruncomplete" now gets the name of table dt2 and then looks for the ":Source table" column, selecting and deleting the "dt2" rows.  It then deletes ":Source table."  Please try that and let me know if this works.

query<<Run(onruncomplete(

dt2=queryResult;
dt2name=dt2<<get name();

try(
	dt1<<select where(:Source Table==dt2name)<<delete rows;
	dt1<<Delete Columns(:Source Table);
);

dt1<<Concatenate(dt2, append to first table,create source column);
Close(dt2,nosave))
	
);

[#3]

Yes! It works as expected now! Thanks Hadley!

[#4]

Perfect!