Write Data from Data Table to Database

DBo_94 — Thu, 22 Aug 2024 13:38:48 GMT

Hello JMP-Community,

I am searching your help here because I didn't found a sufficent solution for myself in other posts. It is about writing content of a data table into a table of a database.

For that I wrote this little function:

myInsertInto = function({dt, dbConnection, destinationName},
	cols = dt << GetColumnNames(String);
	rows = dt << Select All Rows << GetSelectedRows;
	show(cols, rows);

	//prepare sql head
	sqlHead = "Insert Into " || destinationName || " (";
	for each({col, index}, cols,
		if(index == Length(cols),
			sqlHead = sqlHead || col ||")",
			sqlHead = sqlHead || col ||",";
		)
		
	);

	//prepare sql body
	sqlBody = "Values\!n";
	ForEach({row, index}, rows,
		sqlBody = sqlBody || "(";
		show(row);
		values = dt[row,0];
		show(values);

		for each({val, index}, values,
			if(type(val) == "String",
				if(IsMissing(val), 
					tmpVal = "null",
					tmpVal = "'"||val||"'"
				)
				,
				if(IsMissing(val), 
					tmpVal = "null",
					tmpVal = char(val)
				)
			);
			if(index == Length(values),
				sqlBody = sqlBody || tmpVal ||")",
				sqlBody = sqlBody || tmpVal ||",";
			)
		);

		if(index == Length(rows),
			sqlBody = sqlBody || ";",
			sqlBody = sqlBody || ",\!n"
		);

	);

	sqlQurey = sqlHead || "\!n" || sqlBody;

	New SQL Query(
		Version( 130 ),
		Connection( myDbHandle ),
		QueryName( "messwert.test_struktur_dennis" ),
		Custom SQL(
			Substitute(sqlQurey,"\!n", "", "\!t", "")
		)
	)<< run;

	return(sqlQurey)
);

The function does what it should, but there are limitations that I don't know how to solve. For example, we are using a postgresql database in which tables have timestamp, date or other formatted-columns. I don't have an idea how to include that into my function, because JMP is seeing all these formatted types as a number.

Another issue is to handle data duplicates. With my function the content of a data table would be blindly inserted into our database. That can lead to duplicates, because I don't know what is already there. I found in another post this function:

dt << Save Database( dbc,

"dbo.fake",

Replace

);

Unfortunately, that would replace my whole table, which i can not use.

Therefore I'd like to ask the community for help. Does anyone know about an add-in or a script solution, that solves these issues?

topic Write Data from Data Table to Database in Discussions

Write Data from Data Table to Database