Sometimes there is needed single-use data import (e.g. from xls) to the table with many obligatory fields, that are the same for all imported rows.
I have created simple xls tool for generating INSERT queries:
This sample sheet has been created for importing Codes and products Names to Simplicor database.
Usage:
- Validate your data (remove duplicities, etc.)
- Write/Copy data to the xls tool:
Sheet Defaults: There are located attributes and their values, that are the same for every record of your case.
First put name of db table (yellow field - cell B2).
Look into database table for (namely obligatory) “default” fields and write their names and values into the sheet (grey area - column A, B - line 4-199). Every attribute Name has to have its Value. Quote string values by apostrophes here. Simply delete/change values as necessary (but do not remove lines/columns).
Sheet Data: Put the correct attributes names to the yellow header (line 1). Insert variable data (max. 10 columns - A-J, line 2-50000). It has to be at least one column filled in.
All output variable values are automatically quotated by apostrophes.
- Copy all output records (blue rows in column W named “Query”)
- Check the output and use it like db script.
If you wand edit some part of the tool, just unlock sheets - password is blank.
Thanks Tomas for the idea.