[thelist] RE: dummy database data

Brooking, John John.Brooking at sappi.com
Tue Jan 18 20:50:18 CST 2005


Here's a trick I use sometimes. Hope it's helpful.

<tip author="John Brooking" type="Easy dummy data">

Need an easy way to enter any amount of test data into your development
database? If you don't have access to a database tool that gives you a
spreadsheet-like table interface for entry, use a real spreadsheet with
formulas instead. Enter the fieldnames in the first row, and as many
rows of data as you want. Then to the right of the last column, on the
first data row, enter a formula to create a SQL "INSERT" statement from
the values. Be careful to use quotes where necessary for character
fields, and whatever date format your database expects. It can be a
little painful getting the formula just right, but once it's correct,
you just copy and paste for the rest of the rows, and run it as a
script! (Remember to put a semi-colon or other appropriate separator at
the end of each statement.)

For example (using Excel syntax), to enter values for a table with
fields "ClassDate", "StudentCount", and "Teacher" in colums A-C, your
formula in the first data row (row 2) might be:

    ="INSERT INTO ClassTable ( " & A$1 & ", " & B$1 & ", " & C$1 & " )
VALUES ( '" & Text(A2, "dd-mmm-yyyy" ) & "', " & B2 & ", '" & C2 "' );"
    
Note the A$1, etc, instead of hard-coding the field names. You could
hard-code them, but this is slightly more versatile, besides looking
more impressive. ;-) The $ in front of the row portion of the cell
reference means that this row number will stay constant as you copy the
formula down the rows. So the next row will still use A1-C1 for the
field names, but A3-C3 for the data, and so on.

If your first row of data is "Febuary 1, 2005", "15", and "Mr. Chips",
your generated statement should be:

   INSERT INTO ClassTable ( ClassDate, StudentCount, Teacher ) VALUES (
'01-FEB-2005', 15, 'Mr. Chips' );
   
The point of this whole excercise is to remove the tedium from the data
entry process by reducing it to as few keystrokes as possible, and
having the formulas do the work of putting it into the context of SQL
statements.

</tip>

-- 


This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.



More information about the thelist mailing list