How to Insert(Append/Write) data to SQL Server database in table from JMP dataTable through script

[#1]

Hello,

I scripted data preparation / data cleaning with various formula / formats and output data is ready for Insert(Write) to SQL server from JMP table. I connected to SQL, but not able to insert(append) data to SQL server database on target table. Can any one help on this situation to overcome on SQL statement to append. Here is my end script which is ready (JMP Table = "Upload") to write. 

Data Table( "ServerData" ) << Join(
	With( Data Table( "DataProcessed" ) ),
	Merge Same Name Columns,
	Copy second table formula( 0 ),
	Suppress main table formula evaluation( 0 ),
	By Matching Columns( :Individual_Claim_Number_ID_Full = :Individual_Claim_Number_ID_Full ),
	Drop multiples( 0, 0 ),
	Name( "Include non-matches" )(0, 1),
	Preserve main table order( 1 ),
	Output Table( "Upload" )
);

dbc = Create Database Connection(
	"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MAWA_REP;Data Source=USFLI-WAS101"
);

sqlreturn = Execute SQL(
	dbc,
	"INSERT Upload INTO [dbo].ALL_CUSTOMERS"
);
Close Database Connection( dbc );

[#2]

SQL has no idea what upload is, 

You might be able to do 

dt = Data Table( "ServerData" ) << Join(
	With( Data Table( "DataProcessed" ) ),
	Merge Same Name Columns,
	Copy second table formula( 0 ),
	Suppress main table formula evaluation( 0 ),
	By Matching Columns( :Individual_Claim_Number_ID_Full = :Individual_Claim_Number_ID_Full ),
	Drop multiples( 0, 0 ),
	Name( "Include non-matches" )(0, 1),
	Preserve main table order( 1 ),
	Output Table( "Upload" )
);

dt << Save DataBase("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MAWA_REP;Data Source=USFLI-WAS101", "dbo.all_customers");

But I'm pretty sure that would actually overwrite your sql table.  

You could write the sql dymanically.  Just for through your table to put in all the values into a SQL string that you can then run. 

INSERT INTO [dbo].[ALL_CUSTOMERS]
           (col1, col2)
     VALUES
           (col1_value1, col2_value1), 
           (col1_value2, col2_value2), 

[#3]

Sorry, i didn't add complete script and data table which is ready to append in my initial question.

Here i attached the script and data table for append in SQL server. 

Data table has 224 columns and also in correct order based on SQL table with same field name. Number of rows will change every time, so append the data to SQL by statement based on upload.jmp table rows.

//Data Table "Upload" Ready to Append to SQL Server

dt << Save DataBase("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MAWA_REP;Data Source=USFLI-WAS101", "dbo.ALL_CUSTOMERS");

INSERT INTO [dbo].[ALL_CUSTOMERS],
(col1, col2)
VALUES
(col1_value1, col2_value1),
(col1_value2, col2_value2),

[#4]

I think SAVE DATABASE overwrites the table.  If you want to append data you can create a SQL string for multiple inserts and run the SQL with EXECUTE SQL.

[#5]

Thanks.. 

Can you show me SQL with execute SQL with an example. so that i can follow your step and create scripts according yours.

[#6]

Something like this will do what you want.  I'm not handling dates - that will require additional logic.  Not sure how SQL server handles dates for inserts.  

dt = data table ("Upload");

col_list = dt << get column names(string);
col_names = concat items(col_list, ", ");

insert_prefix = evalinsert(
"INSERT INTO [dbo].[ALL_CUSTOMERS] (^col_names^) VALUES ");

dbc = create database connection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MAWA_REP;Data Source=USFLI-WAS101");
for (i = 1, i <= nrows(dt), i++,
	
	for (k = 1, k <= ncols(dt), k++,
		one_col = col_list[k];
		one_value = column(dt, one_col)[i];
// Surround strings with single quotes
		if (type(one_value) == "String",
			one_value = "'" || one_value || "'";
			,
// Convert numeric to string, handle missing values (.)
			one_value = char(one_value);
			if (one_value == ".", one_value = "NULL");
		);
		if (k == 1,
			sql_string = one_value;
			,
			sql_string = sql_string || ", " || one_value;
		);
	);
	insert_sql = insert_prefix || "(" || sql_string || ")";
	show(insert_sql);

// Do the insert.  Execute SQL does an implicit commit
	execute sql(dbc, insert_sql);
);

close database connection(dbc);

[#7]

Hello pmroz,

I tired many ways, there is no error message. But data not insert in SQL table. 

I used same your scripts for another table, that works fine. Data of row was correctly append in database table. But this table has only 2 columns(fields). May be that works correct.

But when i tired for insert in "ALL_CUSTOMER", scripts running smoothly but data not added in database has insert. In real time i have many blanks in values, may be (Not Sure) due to that Insert not working? Here i attach full scripts, SQL column properties and data ready to upload(JMP File) in SQL table for your reference.

Can you check the dates also, if any error. 

I completed 90% of scripts, struggling in data upload to SQL which makes project 100% complete. 

Thanks for your kind help.

[#8]

Use the show() or write() command to display the sql statement to the Log window.  Copy/paste that into a SQL Server front end (e.g. SQL Management Studio) - that should give you better error messages.  I suspect that the blanks are what is giving you trouble.  Figure out how to do that "manually" in SQL server and you'll be able to make it work from JSL.  I don't have SQL Server so can't troubleshoot beyond that.