topic Saving to a SQL Database from a JMP Table: Does the Original SQL Table Have to be Replaced? in Discussions

Saving to a SQL Database from a JMP Table: Does the Original SQL Table Have to be Replaced?

SRoweton — Wed, 08 Nov 2017 20:12:33 GMT

I would like to add data to my SQL database directly from JMP tables without modifying column deisgnations already created in my SQL database. However, when I use the "Save to Database" script that is provided in JMP, the JMP table completely replaces the table in the SQL database. This modifies all of the column properties in the database table (e.g., changing an Integer designation to Float). Is there any way to just append or add new JMP data to an existing table in a SQL database--instead of completely overwriting and replacing the DB table? Can column designations for things like variable type and database keys be preserved when adding data directly from JMP to a SQL database?

Re: Saving to a SQL Database from a JMP Table: Does the Original SQL Table Have to be Replaced?

pmroz — Thu, 09 Nov 2017 13:28:24 GMT

If you want to add rows to a table don't use SAVE TO DATABASE. You can use INSERT INTO or INSERT ALL to add rows to an existing database table. INSERT ALL is useful for inserting large numbers of rows.

Here's a simple insert example:

dbc = create database connection("YOUR-CONNECTION-STRING-GOES-HERE");
one_id = "1121121";
sql = evalinsert(
"INSERT INTO myschema.my_activity_log 
		(user_created, date_created, my_section, activity) 
 VALUES ('^one_id^', CURRENT_DATE, 'DASHBOARD', 'REFRESH')"); 
// Run this SQL.  No table is retrieved as it's a simple insert
execute sql(dbc, sql);
// Close the database connection - end of initialization queries
Close Database Connection(dbc);

You can find more information on the INSERT ALL command (for Oracle) here: https://www.techonthenet.com/oracle/questions/insert_rows.php

(edit to correct JSL)

Re: Saving to a SQL Database from a JMP Table: Does the Original SQL Table Have to be Replaced?

SRoweton — Thu, 16 Nov 2017 15:31:45 GMT

Thank you for your help!