cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
SRoweton
Level I

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

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?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

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

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)

View solution in original post

2 REPLIES 2
pmroz
Super User

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

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)

SRoweton
Level I

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

Thank you for your help!