How to run multiple "update from database" table scripts run at once without deleting columns?

[#1]

Hello!

I have a data table that was produced via a script running two separate queries (that provide data from two different sources) which are then merged to form a data table. My question is with regards to updating this table daily in a way that the script doesn't need to be re-run from the start and any cleaning/adjustments/new columns can be kept. I see on the side bar there is both an "update from database" and "update from database2". I can run one of them, but it will delete the columns that are not included from that source (i.e., it I run "update from database", which is for the first query, it will delete columns TVC, VI, AC and DC from query 2 along with other calculation/manual entry columns I add to the table after merging in the original script).

I have tried copying the two update scripts together into one script (see below) but have the issue where when it runs the first one it will try to delete the columns before running the second. I'm not sure if the best approach would be to combine the tables and then try to update or if there is something simpler (maybe a way to have it update without deleting data that doesn't come from that source)? I removed the connection information and changed around names in the code below, but hopefully this helps to give an idea. Has anyone dealt with a similar problem before? Thanks!

New SQL Query(
	Version( 130 ),
	Connection(
		""
	),
	QueryName( "query1" ),
	Select(
		Column( "Assay", "t1" ),
		Column( "Experiment", "t1" ),
		Column( "Date", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ),
		Column( "G", "t1" ),
		Column( "L", "t1" ),
		Column( "N", "t1" ),
		Column( "K", "t1" ),
		Column( "C", "t1" ),
		Column( "H", "t1" ),
		Column( "O", "t1" ),
		Column( "C", "t1" ),
	),
	From( Table( "Runs", Schema( "schema1" ), Alias( "t1" ) ) ),
	Where(
		Contains(
			Column( "Experiment", "t1" ),
			"E001",
			UI(
				Contains(
					Base(
						"Categorical",
						Prompt(
							"Experiment ID:",
							Character,
							PromptName( "t1.Experiment_1" )
						)
					)
				)
			)
		) & Does Not Contain(
			Column( "Assay, "t1" ),
			"xxx",
			UI(
				Contains(
					Base(
						"Categorical",
						Prompt(
							"Add a filter for names (optional) or put xxx:",
							Character,
							PromptName( "t1.AssayId_1" )
						)
					)
				)
			)
		) & GE(
			Column(
				"Date",
				"t1",
				Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
			),
			0,
			UI(
				Comparison(
					Base(
						"Continuous",
						Prompt(
							"Date range:",
							Character,
							PromptName( "t1.RunDate_1" )
						)
					)
				)
			)
		)
	)
) << Run Foreground( UpdateTable( Current Data Table() ) );

New SQL Query(
	Version( 130 ),
	Connection(
		""
	),
	QueryName( "query2" ),
	Select(
		Column( "Assay", "t2" ),
		Column( "Experiment", "t2" ),
		Column( "Date", "t2", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ),
		Column( "TVC", "t2" ),
		Column( "VI", "t2" ),
		Column( "AC", "t2" ),
		Column( "AD", "t2" )
	),
	From( Table( "Runs", Schema( "schema2" ), Alias( "t2" ) ) ),
	Where(
		Contains(
			Column( "Experiment", "t2" ),
			"E001",
			UI(
				Contains(
					Base(
						"Categorical",
						Prompt(
							"Experiment ID:",
							Character,
							PromptName( "t2.Experiment_1" )
						)
					)
				)
			)
		) & Does Not Contain(
			Column( "Assay", "t2" ),
			"xxx",
			UI(
				Contains(
					Base(
						"Categorical",
						Prompt(
							"Add a filter (optional) or put xxx:",
							Character,
							PromptName( "t2.AssayId_1" )
						)
					)
				)
			)
		) & GE(
			Column(
				"Date",
				"t2",
				Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
			),
			0,
			UI(
				Comparison(
					Base(
						"Continuous",
						Prompt(
							"Date range:",
							Character,
							PromptName( "t2.RunDate_1" )
						)
					)
				)
			)
		)
	)
) << Run Foreground( UpdateTable( Current Data Table() ) )

[#2]

I'm pretty sure what you are seeing is that if you add columns to a table that has been created from New SQL Query, you'll get a new datatable when you do an Update from Database. That is, you cannot re-use the imported table since you've changed the metadata (added columns) to it.

I think what you can do is import your 2 tables from the database(s) using the File->Database->Query Builder (don't save the data tables) Then run the Tables->JMP Query Builder to combine the 2 tables. It will looks something like:

New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["Big Class Families" =>
		"Source(New SQL Query(
	Connection(
		""
	),
	QueryName( \!"Big Class Families\!" ),
	Select,
	From( Table( \!"Big Class Families\!", Schema( \!"SQBTest\!" ), Alias( \!"t1\!" ) ) )
) << Run Foreground)",
		"g4_bigclass" =>
		"Source(New SQL Query(
	Connection(""),
	QueryName( \!"g4_bigclass\!" ),
	Select,
	From( Table( \!"g4_bigclass\!", Schema( \!"SQBTest\!" ), Alias( \!"t1\!" ) ) )
) << Run Foreground)"
		]
	),
	QueryName( "SQLQuery3" ),
	Select(
		Column( "picture", "t1" ),
		Column( "name", "t1" ),
		Column( "age", "t1" ),
		Column( "sex", "t1" ),
		Column( "height", "t1" ),
		Column( "weight", "t1" ),
		Column( "sibling ages", "t1" ),
		Column( "sports", "t1" ),
		Column( "countries visited", "t1" ),
		Column( "family cars", "t1" ),
		Column( "reported illnesses", "t1" ),
		Column( "age vector", "t1" ),
		Column( "pet", "t1" ),
		Column( "name", "t2", Alias( "name 2" ) ),
		Column( "age", "t2", Alias( "age 2" ) ),
		Column( "sex", "t2", Alias( "sex 2" ) ),
		Column( "height", "t2", Alias( "height 2" ) ),
		Column( "weight", "t2", Alias( "weight 2" ) )
	),
	From(
		Table( "Big Class Families", Alias( "t1" ) ),
		Table(
			"g4_bigclass",
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "name", "t2" ), Column( "name", "t1" ) ) &
				EQ( Column( "age", "t2" ), Column( "age", "t1" ) ) &
				EQ( Column( "sex", "t2" ), Column( "sex", "t1" ) ) &
				EQ( Column( "height", "t2" ), Column( "height", "t1" ) ) &
				EQ( Column( "weight", "t2" ), Column( "weight", "t1" ) )
			)
		)
	)
) << Run;

What happens is that the JMP Query Builder pulls the source script out of each unsaved table to build its script. What that means is that each time the New SQL Query script runs, it will bring in new data from the data sources in each of the dependent tables.