[thelist] Tip: Using Excel to generate INSERT statements

John.Brooking at NA.SAPPI.COM John.Brooking at NA.SAPPI.COM
Mon Sep 15 13:06:16 CDT 2003

Excel-related tip 2 of 2, as promised:

<tip type="Excel-generated INSERT statements" author="John Brooking">

If you have some small amount of tabular data to insert into a database
table, and if you are an application developer whose main tool is a SQL
query processer (ie you don't have access or familiarity with the type of
loading software that DBA's often use), you can save yourself some typing by
getting your data into an Excel spreadsheet, then using formulas to create
the appropriate "INSERT INTO ..." statements for each row.

Say you have 20 rows of 4 columns. Put them in a spreadsheet, say in rows
1-20, columns A-D. Then in the first line, column E, put the formula:
="INSERT INTO tablename ( fieldname1, fieldname2, fieldname3, fieldname4 )
VALUES ( " & A1 & ", '" & A2 & "', '" & A3 & "', '" & A4 & "' );". Note the
use of single-quotes to surround the string fields, or not if the field is
numeric. (This example assumes all strings but the first one.) Then simply
paste the formula down for as many rows as you have. If your SQL tool can
handle multiple ; separated statements, you can then just copy/paste the
whole resulting sequence at once into your SQL tool and execute it. If you
are using a command-line SQL tool, you may have to copy/paste each line, but
it's still faster than typing each character.

Other options for quick entry: Your SQL tool may have a spreadsheet-like
quick edit form. If you have MS Access and an ODBC driver for your DB, you
can link to your table and type the data into Access. Or, of course, use
whatever loading software your DB has.

This of course should be limited to small amounts of data, not hundreds of
thousands of records.


- John

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