How to insert or update datatable into database using script

[#1]

Hi, everyone.
I'm using JMP 17. I want to insert or update the data in datatable into external database ssms.

Currently, I have a datatable. I want to compare the row of datatable. If the row of datatable is not exist in database then insert into database, else if it is existed in database then update into database.

I know the SQL have insert or update function but don't know the JMP script can make it or not.
what is the syntax or script to insert or update data?

This is SQL  insert or update function

-- Insert or update into Table
		
	MERGE INTO Report.dbo.Table AS target
	USING #tempTable AS source
	ON 
	target.logID = source.logID
	WHEN MATCHED THEN		
	UPDATE SET		
	Value = source.value
	WHEN NOT MATCHED THEN
	INSERT (ID, ProductName, Time, Value)
	VALUES (source.id, source.productname, source.time, source.value);
  

Thanks in advance

[#2]

You can insert/update database tables using JSL no problem.  Your statement looks like it's updating report.dbo.table from the table #tempTable.  If #tempTable is not very large you could create this table in your database and then run your merge script.

Here's some untested code that shows what you need to do.  I have rarely used the save database command; you'll need to experiment to get this working.

dt = New Table( "Source Table", Add Rows( 3 ),
	New Column( "id", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 3] ) ),
	New Column( "productname", Character, "Nominal",
		Set Values( {"AAAAA", "BBBBB", "CCCCC"} ) ),
	New Column( "time", Character, "Nominal",
		Set Values( {"7:30", "8:30", "9:30"} ) ),
	New Column( "value", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [22, 24, 33] ) )
);

// Copy the table to the database
dt << Save Database( "Connect Dialog", "#tempTable" );

// Create a connection to the database
dbc = create database connection("<connection-string-goes-here");

// Run the merge command
sql_statement = evalinsert(
"	MERGE INTO Report.dbo.Table AS target
	USING #tempTable AS source
	ON 
	target.logID = source.logID
	WHEN MATCHED THEN		
	UPDATE SET		
	Value = source.value
	WHEN NOT MATCHED THEN
	INSERT (ID, ProductName, Time, Value)
	VALUES (source.id, source.productname, source.time, source.value)";

execute sql(dbc, sql_statement);
close database connection();