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
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></CONNECTION-STRING-GOES-HERE>
