You lost your leading zeros of your varchars importing/exporting your data. You have this example data in your table storagebins: area = L, row = 1, rack = 2, shelf = 3 and it is supposed to look like this area = L, row = 001, rack = 02, shelf = 03 Use this SQL [...]
Archive for the ‘mySQL’ Category
November 7, 2011 by marco · Leave a Comment
Filed under: Snippets, mySQL
Social Tagging: mySQL • script
Filed under: Snippets, mySQL
Social Tagging: mySQL • script
November 1, 2011 by marco · Leave a Comment
Filed under: Snippets, VBA, mySQL
Social Tagging: development • mySQL • script
Filed under: Snippets, VBA, mySQL
Social Tagging: development • mySQL • script
I used this hacked Excel-Macro to convert all dates in a selected range from a manual data import using Excel to generate all required INSERTs for our MySql database from 03.08.2011 16:51:01 to 2011-08-03 16:51:01 In case you wonder, where you can find your Visual-Basic editor and execute your VBA-code… follow these instructions. Here is [...]
January 14, 2009 by marco · Comments Off
Filed under: Snippets, mySQL
Social Tagging: cli • mySQL • php • script • variables
Filed under: Snippets, mySQL
Social Tagging: cli • mySQL • php • script • variables
Case: I was building a CLI script to aggregate data from one MySql database and write results to another MySql database using 2 open db-connections. This way the script’s execution time was in the some-minutes-range… not good! I tried a more effective attempt: I let the CLI script output all necessary statements to drop and [...]
Case: I had many .sql-files from a backup containing one table-dump each which should be loaded into another MySql-DB. Simply using phpMyAdmin would not have worked with regard to the size of the tables. So I was thinking about a way to do it on the commandline under Linux. A foreach-solution would have caused me [...]
I am currently migrating MySQL databases. One DB uses InnoDB tables with many foreign-key constraints. In this case you can not just dump your CREATE-TABLEs and INSERTs into an empty database. This would lead to many relation violations and incomplete imports, since records are not imported in ‘the right order’ by the dump. To prevent [...]
A nice shortcut I came across to update content in a mySQL-table without writing a script to do it – which was the first thing that came to my mind… Case: I had many HTML-snippets in a CMS table called ‘freepages’ containing hard coded domain-names in URLs to images etc. Now guess what happened as [...]
I have been struggeling many times to do initial filling of databases for projects – especially if the data is a deliverable of a customer and the database structure is already fixed. So I built a VBA macro that generates SQL statements for creation of tables and data inserts from a given XLS-file. The idea [...]
RSS