How can I generate an INSERT script for an existing SQL Server table that includes all stored rows?

sql-server ssms
citronas picture citronas · Feb 21, 2011 · Viewed 260.3k times · Source

I'm looking for a way to generate a "Create and insert all rows" script with SQL Management Studio 2008 R2.

I know that I can create a "create table" script.

I can also create an "insert in" script, but that will only generate a single row with placeholders.

Is there a way to generate an insert script that contains all currently stored rows?

Answer

Thomas Rushton picture Thomas Rushton · Feb 21, 2011

Yes, but you'll need to run it at the database level.

Right-click the database in SSMS, select "Tasks", "Generate Scripts...". As you work through, you'll get to a "Scripting Options" section. Click on "Advanced", and in the list that pops up, where it says "Types of data to script", you've got the option to select Data and/or Schema.

Screen shot of Advanced Scripting Options

Related questions

How do I set a column value to NULL in SQL Server Management Studio?

How do I clear the value from a cell and make it NULL?

Read More
Insert Data Into Temp Table with Query

I have an existing query that outputs current data, and I would like to insert it into a Temp table, but am having some issues doing so. Would anybody have some insight on how to do this? Here is an …

Read More
How to see query history in SQL Server Management Studio

Is the query history stored in some log files? If yes, can you tell me how to find their location? If not, can you give me any advice on how to see it?

Read More