.-
help for ^tosql^                    (Statalist distribution, 26 March 2001)
.-

Write data to be imported to SQL database
-----------------------------------------

    ^tosql^ [varlist] {^if^ exp] {^in^ range] ,^Table^(string) [^Case^ ^Cre^ ^Upper^]


Description
-----------

^tosql^ facilitates the transfer of data, via ASCII text, to a SQL database
such as Oracle or mySQL. If executed in simplest form, ^tosql,table(filename)^,
it creates the file ^filename.sql^, containing the SQL INSERT instructions
to load each row into the preexisting table. This SQL instruction (in 
comparison with "bulk load" facilities, which differ by database package) 
permits rows to be appended to an existing SQL table. If files with
those names already exist, they will be overwritten. 

If no ^varlist^ is given, all variables are transferred to the output files.
An explicit ^varlist^ overrides this behavior. String variables' values will
be enclosed in single quotes to ensure proper handling by SQL. Embedded single
quotes will be replaced with underscores.

It must be noted that not all Stata variable names are legal SQL column 
names. For instance, "foreign" in auto.dta is a reserved word in SQL. 
The ^rename^ command should be used to change the variable name before
executing ^tosql^.

^tosql^ makes use of the ^file^ command, added to Stata version 7 on 8 May
2001. If you do not have this command, update your copy of Stata. If you are
using Stata version 6, use ^tosql6^, which is functionally equivalent but
makes use of logfiles. 


Options
-------

^table^ is required; the provided string must be a valid SQL table name.

^case^ specifies that casewise deletion should be performed: observations in
which any missing values are encountered will not be transferred to the output
file. By default, numeric missing values will be replaced by the string NULL
and string missing values will transfer as null strings.

^cre^ specifies that the table creation script is to be written to a separate
file. This script will take the datatypes from Stata (including the length of
character fields) and write the appropriate CREATE TABLE statement. However,
the routine writes generic code, and does not include the implementation-
specific parameters that may be required in some SQL environments. The file
may be edited to provide that additional syntax. The file is named 
cre_^table^.sql (or its upper-case equivalent--see below); if a file with
that names already exists, it will be overwritten. 

^upper^ specifies that the table name and column names are to be expressed
in upper case. This option also changes the name of the output file to 
^TABLE.SQL^, which users of case-sensitive operating systems should heed. If
the ^cre^ option is used, the name of that file and its contents will be
upper case when the ^upper^ option is specified.


Example
-------

	. ^use auto^

	. ^tosql  make price mpg rep78,table(auto4)^
	
	. ^tosql  make price mpg rep78,table(auto4) upper^
	
	. ^tosql  make price mpg rep78,table(auto4c) case cre^
	
	. ^!mysql dbname <cre_auto4c.sql^
	
	. ^!mysql dbname <auto4c.sql^
	

Acknowledgements
----------------
The need for this routine was suggested by Achille Manfredi Luongo, who has
provided helpful feedback. Thanks also to Nick Cox for programming tips. I am 
most appreciative of Bill Gould's efforts to add file input/output capabilities
to Stata, which vastly improve the performance of this routine on large 
datasets.

 
Author
------

Christopher F Baum, Boston College, USA
baum@@bc.edu


Also see
--------

On-line:  help for @log@, @torats@ (if installed), @outseries@ (if installed)

