Loading a sql file into Stata

I just replied to an older, but unanswered, Stata post on Stackoverflow that dealt with reading .sql files in Stata. The poster wanted to take a dataset in the form of a .sql file and import it into Stata. A .sql file is just a collection of SQL commands, stored in a text file. These files can be used to backup a database and can be run in bulk to re-create a database.

To my knowledge, it’s not possible to import from a .sql file directly, so I suggested that the poster use Stata to import the .sql file into a database and then load it into Stata using -odbc-. The commands for doing this would be:

odbc sqlfile("dataset.sql"), dsn("DataSourceName")
odbc load, exec("SELECT * FROM MyTable") dsn("DataSourceName")

where dataset.sql is the name of the file containing your set of SQL commands, DataSourceName is the name of and ODBC connection setup using your ODBC manager of choice for your operating system, and MyTable is a table in the database from which you’d like to load data.

Although this may be slow to import the data, especially if the database is large, using a database has one significant advantage over loading data from a text file in that the database can be queried. So, instead of loading an entire dataset into Stata’s memory, one can load a subset using the SQL language, which I won’t get into at this time.

So, am I right that Stata cannot read a .sql file correctly? If so, would you approach this problem in the same way or do something different?

This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

One Response to Loading a sql file into Stata

  1. Saraja says:

    This one is very unique but a rare reply to one of my long outstanding quest to manipulate and process as well as load SQL files or tables into Stata for seamless operations. I will highly appreciate if you put forward some kid of an example to make it even more lucid and straightforward use this tool for all times to come!!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>