3

Is it possible to generate individual INSERT statements for SQL Server records using just Management Studio or Visual Studio?

You can "generate scripts" to get the data for the entire table in SSMS, but not just a single row or filtered set of rows.

In Visual Studio 2012, if you select the table data in SQL Server Object Explorer, you get a "Script" button, which generates insert statements for the grid results, which is either 200, 1000, or "All" records - but I can't find a "WHERE" filter, like there is in SSMS.

Is there any trick that I'm missing? Either SSMS or VS would be great - ideally I'd be able to filter a specific row or set of rows, then generate INSERT statements specifically for these rows.

There's SSMS Toolpack, RedGate, Toad, and I'm sure a handful of other paid products that can do this, but I'm hoping there's some built-in way.

(This question has been addressed before, but all I can find was prior to 2012, so maybe something has been introduced since then).

Community
  • 1
  • 1
Joe Enos
  • 39,478
  • 11
  • 80
  • 136
  • How do you want to filter down the rows and do you want it on one table or multiple tables? You could write a query that would generate the commands for you yourself, but I'm not aware of a "tool" feature that would do it. – Eric J. Price Oct 15 '13 at 22:08
  • @Love2Learn bsivel's answer includes a link to a script someone has written to handle this - I've actually done something similar in the past (but not nearly as complete). Ideally, what I'm looking for is one of two things: either a function in SSMS on the Edit Rows grid (which includes a filter) to "Generate Scripts", or a way to add a WHERE filter to Visual Studio. Just a single table. I find it funny that SSMS gives you half of this functionality and VS the other half. – Joe Enos Oct 15 '13 at 22:16

3 Answers3

1

Yes - use the Generate Scripts Task.

Right-Click on the DB in the Object Explorer window. Then select Tasks and Generate Scripts.

Select the object(s) you want to script and click next. Then click Advanced and go to "Types of Data to Script". Choose Data Only or Schema and Data (Theses selections will build your insert statements).

It sounds like you already know about this though and I'm not aware of any way to apply a filter on what is generated.

Here is a script I found on SqlServerCentral. Although this was built for sql server 2k, its worth a look:

http://vyaskn.tripod.com/code/generate_inserts.txt

bsivel
  • 2,821
  • 5
  • 25
  • 32
0

May be this answer is too late , those who started using 2012 recently will be helpful.

In VS 2012 , Go to the SQL Server Object Explorer .Select Database ,then list the table inside that DB. Then select the particular/specific table which script you wanted to create.Then Right click on that table and select ViewData option. Then in the top of that display/result Grid , Right to the Maxrow, you can see two icon. Both are generating the script. One on the screen and another into a File.

user1749782
  • 27
  • 1
  • Unfortunately, I don't have a "ViewData" option in my options when I right-click on a table. Do you have an extension installed? (I think our hope was to find a solution that doesn't include "install extension".) – RobertB Mar 14 '14 at 16:14
  • 1
    @RobertB I don't think this requires an extension, but I believe it requires Premium or Ultimate edition. – jlnorsworthy Jul 08 '15 at 19:13
0

Create a temporary table using the following query:

SELECT * 
INTO #TempDestinationTable
FROM {your table}
WHERE {your condition}

This will create a new table #TempDestinationTable.

Now, you can follow this: What is the best way to auto-generate INSERT statements for a SQL Server table? to generate the insert statements.

Chris Catignani
  • 5,040
  • 16
  • 42
  • 49
Gourav kumar Shakya
  • 1