SSMS - Generate Scripts - Data Only doesn't work with views?

Question

I am trying to generate a data only script for a view using SSMS Generate Scripts feature. However, even though I select Data Only, it still only generates the view definition and not the data generated by the view. Is there something else I need to do?

I'm using SSMS for SQL Server 2014.

Well, after digging for a while, I found a thread that might be helpful. Unfortunately, I don't know what your settings look like for the insert. Go down a couple of answers on this thread and take a look at the settings and see if yours match. http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table — SQL Taylor, Mar 17 '15 at 17:26
Thanks but the thread you reference is almost 6 years old and a lot has changed with SSMS since then. The settings screen has changed quite a bit. — Randy Minder, Mar 17 '15 at 17:35
Yep, that's all I could find. It doesn't look like a lot of people are having this issue based on the lack of people asking the question. A pretty intensive internet search yielded nothing for me and mine is working fine. You could always use another method. There are a couple of stored procedure scripts in that other thread I linked that will accomplish what you're trying to do. Sorry I couldn't be of more help. — SQL Taylor, Mar 17 '15 at 18:23

score 2 · Accepted Answer · answered Feb 17 '17 at 20:42

I know this is old, but I will answer it for other people who stumble on it.

Generate Scripts -> Data Only is bugged for views.

The easiest option without searching for other stored procedures or external tools is to copy the view contents into a table. Generate Scripts -> Data Only works fine with tables.

For example,

SELECT * 
INTO NEWTABLE
FROM dbo.Component

Then you can do Generate Scripts on the NEWTABLE and select Data Only in Advanced and it will work.

You can then delete the NEWTABLE.

score 0 · Answer 2 · answered Jan 30 '20 at 14:22

Given that Generate Scripts still doesn't appear to work for view data as of SSMS v17.9.1, an alternative depending on your needs might be to use the SQL Server Import and Export Wizard. You can read data from a view and write it to a table, across different databases and servers without resorting to a linked server.

score -1 · Answer 3 · answered May 19 '22 at 20:50

SSMS is still poor at this, VS has been able to do this for a while

Use menu VIEW->SQL SERVER OBJECT EXPLORER

Create a new server Navigate down to your table or view , right click -> View Data

use the filter to limit the dataset to what you are interested in

Then use the SCRIPT command (also available on context menu)

This works for views and tables. Not super easy, but ill give it A-. Way better than other hacks that used to be available (including SSMS.ExportData which is not great)

hope that helps someone. I just had to export some rows and had to re-remember how to do this. hope it helps someone...

greg

SSMS - Generate Scripts - Data Only doesn't work with views?

3 Answers3