# How to Export List in SQL Format?

Posted 7 years ago
10139 Views
|
6 Replies
|
4 Total Likes
|
 Hello Wolfram Forum,I have data in list format (e.g. {{a},{b},{c}), with all entries being strings. How can I export this for SQL? The output format looks like, update set = where = and =; The above {"a","b","c"} correspond to each row in the text file to be used for MySQL. "" is a variable/value, removed here to make this generic. I have the code that concatenates the variables into rows and makes it a string, ?ToString[ ? "update tableName set fieldName= " <> ? ? ToString[variable] <> ? ? " where fieldName='" <> ToString[variable] <> "' and field='" <> ? ? ToString[variable <> "';" ? ?] One problem is that the list format ends the line with a comma (i.e. ,). Also, it begins the list with {{ and end encapsulates each row with { }. I haven't been able to get the "SQL" option for Export to work in Mathematica version 10 and haven't come across an example online. Maybe I should export it in some other text format?Thanks,Greg
6 Replies
Sort By:
Posted 7 years ago
 Greg, MySQL is very efficient importing delimited text. Why do you want to create a SQL file?
Posted 7 years ago
 Idea is to avoid another step. I am intending to go through this update and importing process a few hundred separate times over the next few weeks.
Posted 7 years ago
Posted 7 years ago
 I'll look into it- thanks.
 This guide is very complete: DatabaseLinkUserGuideHere is how I would do for your case. Needs["DatabaseLink"] conn = OpenSQLConnection[ JDBC["Microsoft SQL Server(jTDS)", "myIP"] ,"Username" -> "myuser" ,"Password" -> "mypass" ] list={{"a1","b1","c1"},{"a2","b2","c2"}} sql="update tableName set fieldName=1 where fiedName = 2 and field = 3" SQLExecute[conn,sql,list] CloseSQLConnection[conn] `