Last Friday before leaving work, I had to make a database dump to copy some data over another SQL Server. I had always used the DTS packages to export or import data, however when using a DTS package you need to know the Server Name and login credentials for both the source server and the destination. You can also import or export to different file formats such as excel sheets, access databases, comma separated files .csv or text files.
I didn’t know the name of the destination sql server, nor the credentials to log into that server, one of the requirements from the other team was to get the DTS package already, not only the files with the data.
I then though, well, I should be able to generate a .sql file with the insert statements, this way all they have to do is run the query in the Query Analyzer. To my entire surprise, I was totally unable to do that with the Enterprise Manager or the Query Analyzer. The Query Analyzer would only give me an insert template whenever I right click on the table name and selected the insert statement.
I ended up creating a .dts file to import data from a previously generated .cvs file. On this package, the server name and credentials have to be edited before running in the final environment.
I then thought, well, wouldn’t it be a lot easier to just have the insert statements. Let’s take a look at MySQL and the most primitive manager it has, phpMyAdmin. I can generate a .sql file that will include the DDL for creating a table or not, and the data for populating that table as a group of inserts.
And save the file with a .sql extension:
It is true that MS SQL Server will let you copy objects among databases, it is true that there is a greater complexity of those objects and the data transformation that you can make with a DTS, but the point is, why they didn’t keep the simplest as well.