Problem Statement —

Sometimes we might need to populate our SQL tables with reference/ look-up data. There are other scenarios when we need to copy table data from one environment to another. In both these cases, we require to create bulk scripts for Inserting data into our destination table.

In this article, I will mention about a quick way to generate the SQL Insert Scripts using SQL Server Database project with just 2 clicks.

How to generate SQL Insert Scripts using SSMS?

SQL Server Management Studio provides a ‘Generate and Publish Script‘ Wizard which helps us to generate the scripts for table schema or data. This is one of the nicest feature of SSMS and is generally used for generating the insert scripts.

Right Click on the Database and go to Tasks -> Generate Scripts

Be default, this wizard generates the scripts for the table schema only. If we need to generate Insert scripts for the data, we will need to go to the ‘Advanced Scripting Options‘ and change the ‘Types of data to script‘ from ‘Schema only’ to ‘Data only’.

I feel this feature is a bit hidden and many developers are not aware of it when they start working in SSMS. To generate the insert scripts you need to leverage this wizard and there are multiple steps involved. Also by default, this feature is turned off.

Now lets look at another quick way to generate SQL Insert scripts.

What is SSDT?

SQL Server Data Tools in Visual Studio basically transforms the traditional database development by allowing us to view, design, maintain and refactor database objects without having to jump from Visual Studio IDE to any other toolset like SSMS.

The database project is a special type of Visual Studio.NET project. Its purpose is to create and manage SQL database objects. It converts the database objects into text files, which can be searched, modified and stored in source control just like your code files.

How to Generate Insert Scripts using Database project?

Open the SQL Server Object Explorer in the Visual Studio IDE.

Right Click on the required table and select ‘View Data’.

There are two options for generating an insert script from the data editor —
1. Select ‘Script’ option to generate the script to another tab.
2. Select ‘Script to File’ option to generate the script to a file.

And once you click on ‘Script’ or ‘Script to File’ button, the Insert Scripts will be generated and available to us. It is as simple as that!

Note – If you need to generate the Insert scripts for all the rows in your table, make sure you select the ‘All’ option in the ‘Max Rows’ field.

—————————————————————————————————————————————–

Pro Tip:

Need to work on the go, then Access your software development environment from anywhere from multiple devices provided by the best Desktop-as-a-Service provider on https://www.clouddesktoponline.com/hosted-virtual-desktop/.

Add Office 365 applications to the desktop with full support from http://www.apps4rent.com/

Categories: SQL Server, SQL Tips