Write data to be imported to SQL database -----------------------------------------
^tosql^ [varlist] {^if^ exp] {^in^ range] ,^Table^(string) [^Case^ ^Cre^ ^U > pper^]
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)