As a DBA or SQL Server developer, you may run into cases where it would be handy to script data stored in tables or views so that it can be transferred to another database. Some of the reasons you might want to do this include:
- To archive data. By scripting data (and then deleting it from the original database), you have a ready-made way to easily and quickly re-add the archived data to the database if it is needed again.
- To send data via an e-mail. Sometimes you need to send data, such as new or updated product information, via e-mail to a distant location, so they can add the updated data to their version of the database.
- To replicate data from one database to another, such as moving sales data from the production database to a sales analysis database.
- To synchronize data, such as between a production and a test database.
- To provide the exact same set of data, over and over again, in order to perform testing of an application under the same exact conditions.
- To check in scripts to source control software.
- To script a database and its data, so that it can be recreated at another location. An example of this is when a developer wants to create a script of a demo or production database for their software product that can be run by an end-user at the time of the software install.
After reviewing the above examples, you might say that SQL Server offers built-in tools to perform these tasks. And in certain cases, you are right. For example, SQL Server’s replication feature or DTS might be a good alternative in some cases. But in many cases, SQL Server’s tools are overkill for what you need accomplished, or they don’t work as conveniently, or perhaps they won’t do exactly what you need, such as automatically creating a Transact-SQL script of the data in a table that can be used to insert the data into another database, a feature that SQL doesn’t automatically provide. If you find yourself with the need to be able to script data in a database, then you might want to take a look a product from ApexSQL Software called Apex SQL Script.
What Exactly Can Apex SQL Script Do For Me? Apex SQL Script is a clever, easy-to-use utility that has one main purpose in life, and that is to script data from SQL Server tables and views. In other words, you tell Apex SQL Script what data you want scripted, and it will evaluate your data and then create a Transact-SQL script that you can then run, such as in a tool like Query Analyzer, to recreate the exact same data in another database. The program is fast, and can script an unlimited number of tables and rows from a database, allowing you to recreate that data in as many databases you need. Should the tables you need to script are related via referential integrity, Apex SQL Script has the ability to figure out the relationships in the tables (in most cases), scripting them in the correct order so that referential integrity problems don’t result when the script is run on a different database. Or, at your option, you can choose to suppress constraints when data is being inserted.
Besides the main ability to script data from tables and views, Apex SQL Script has the ability to script database objects, which can also come in very handy for the busy DBA or SQL Server developer.
How Does Apex SQL Script Work? The best way to find out what Apex SQL Script can do is to see a demonstration. So what I will do now is show you how Apex SQL Script can be used to script some data from the Northwind database. To begin scripting data, the first step is to start the Apex SQL Script program and specify the name of the SQL Server that contains the data you want to script.
As you can see from this screen shot, you can use SQL Server standard security or Windows Authentication to login. Once you are logged in, the next step is to select the database you want to work with. In our case, we will use Northwind.
In the above screen shot (which has been shrunk to make it easier to fit on this page), you are seeing is what is called the Data view. This shows you either all of the Tables or Views that exist in the database that you can create scripts from. In addition, this screen shot only shows the tables. If you want to see the views, then you would select the Views view (not shown here).