If you need to script out the objects within a database, from generating tables through to extracting out the data so you can use it in another database or schema then you are going to need to script the SQL.

MS SQL Server 2008 has new Generate Scripts option which enables you to script data in SQL Server database tables. you can script data from sql tables into a script file, to the clipboard or script data on a new sql query window. Script data can be used to export and/or import table data from one database to another database.

The Script Data option creates INSERT statements foreach row in the table using the column data that the related table record has. Later than the scripted table data can be used by executing the generated t-sql scripts, to create a copy of the original table on an other server or an other database with identical data or identical rows on the destination database or table, what’s more you can script the the database to go to a SQL 2000 database from a SQL 2008, how cool is that?

SQL Server generate script with data is a powerful SQL Server tool in order to create sql script to move data from one database to another database.

I want to demonstrate with a sample how a sql developer can use the Generate Scripts task in order to script table data of a SQL Server 2008 database table.

Open the Generate Scripts SubMenu Item from Task Menu

First of all, open the Microsoft SQL Server Management Studio which is installed from the Client Tools of a MS SQL Server 2008 installation package.

Connect to a MS SQL Server database instance using the Server Explorer or using the Connect screen.

Then open the Object Explorer window and expand the Databases node.

Here I connected to the local SQL Server instance and clicked over the Databases node and a list of existing sql server databases are visible in the object explorer window now. Later, I clicked the sql database MyWorks which owns the tables that I want to script data, rows/records of the database.

Continue by right clicking on the database name and open the context menu, chooes Tasks menu and open submenu. Select Generate Script submenu item from the displated list.

Generate SQL Server Script Wizards

When you select the Generate Scripts sub menu item the Generate SQL Server Scripts Wizard starts. SQL administrators and sql programmers can use the Script Wizard to generate t-sql scripts as a t-sql scripter to create scripts for any object (tables, views, schemas, etc). You can work in detail on the Script Wizard and find useful hint that you can benefit in your sql developments.

Watch the following screen cast to see it in action.