BookmarkSubscribe
Choose Language Hide Translation Bar
susanroweton0
Community Trekker

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?

 

 

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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)

2 REPLIES 2
Highlighted
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)

susanroweton0
Community Trekker

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

Thank you for your help!
0 Kudos