How to generate insert statement for existing data in SQL Server Management studio?


In this article I have explained about how to create insert statement for previously stored data in the SQL Server Database table. Here I used SSMS Tools pack (SSMS tools pack is a add-in for SQL Server management studio). For example we have one table in the database with several data we need to move that table data in to another system SQL Server database same structured table.

First of all download the SSMS Tools pack set up file from the below link. www.ssmstoolspack.com/Download Just Double click the downloaded set up file and install like this.

picture 1


After installation check whether the SSMS tools pack add in the SQL Server Management Studio like below screen.

picture2


I have explained below how to take up back up particular table data with different requirement.
Select Database table which ever data you want back up. Then right click of the table and select SSMS Tools --> Generate Insert Statement .


picture3


After select "General Insert statement" new pop up window is appear like this

picture4


In that pop up window click"Generate Scripts" button for generate insert statement. Results come like this with all data and its database name etc.

picture5


In the pop window first option number of rows "0" (it denotes create insert statement for all records in the table) if you change any other number in the option then can you get that particular row. For example if you put "2" in that option, then Insert statement is generate with first two rows of your selected table.

picture6


Result:

picture7


In the pop window second option is used for enter custom defined condition you can use where or order by conditions in that option. So resulted inserted statement is generated based on your condition.

picture8


After you generate insert statement script just execute that script in other PC. If database name is different in that PC then change first line database to new Database name.

Result:

picture9


Conclusion:
Using this SSMS Tools pack feature we can generate insert statement for several data within a single minute. And also this feature is used to take partial backup (single table and its data) in the SQL Server database. I hope this article is help to you for know about SSMS tools pack usage.


Comments

Author: yuvaraj29 Jul 2011 Member Level: Silver   Points : 0

Excellent!!

Author: Suresh21 Aug 2011 Member Level: Gold   Points : 1

Hi Ravindran,

It is Excellent Article about SSMS Tools pack. It is helped to know about SSMS Tools. Good Explanation.

Very Informative!!!

Keep it Up.

Thanks and Regards
S.Suresh

Author: Ravindran21 Aug 2011 Member Level: Diamond   Points : 0

Thanks Yuvraj and Suresh!

Author: Dotnet Developer-201510 Oct 2012 Member Level: Gold   Points : 0

Hi Ravindran,
working good
but this is able to one server to another server?

Author: Ravindran10 Oct 2012 Member Level: Diamond   Points : 0

Yes Just taken that script and save it and use that for other server same structure table too.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: