#SQLSERVERLearning : Export Table data as Insert statement in SQL Server.

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

With Oracle, SQL Developer and Toad console provide an option to export Table data from Result set itself.
With SQL Server, we would need to follow series of steps to export table as Insert statement.

Lets Iterate through steps to Export table data in SQL Server. (Tested on SQL Server 2012)
For demonstration purpose, we would take “AdventureWorks2012” database as sample data-set.

Step 1:Use Generate Scripts Task for exporting table data.
step1_export_table

Step 2:Select Database object to export
We can select multiple object for export as insert scripts from table or view.
step2_export_table

Step 3:Select Option on how to Save Export Insert Script.
step3_export_table

Step 4:Select Export Type for database object.
Click on Advanced option to change default export properties.
By default only Schema creation script can be exported, if data is also need we can select “Schema and Data Option”.
If only Data is needed, we can select only data options.
step4_export_table

Step 5:Export Summary.
Check and Validate option chosen for export, before proceeding further.
step5_export_table

Step 6:Export Execution Status.
step6_export_table

Step 7:Final Export Script.
step7_export_table

Using above steps, we can export complete set of records for a table.
But in case we need to export only specific set of records from a tables, not sure whether we have any option provided by SSMS.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in SQL Server Learning and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s