Subscribe Bookmark RSS Feed

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

susanroweton0

Community Trekker

Joined:

Oct 15, 2015

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

Joined:

Jun 23, 2011

Solution

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
pmroz

Super User

Joined:

Jun 23, 2011

Solution

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

Joined:

Oct 15, 2015

Thank you for your help!