Generate script in SQL Server 2005 with data

Question

How can I generate a script in SQL Server 2005 with data in table?

Answer 1

Right click on the database. Then select the Tasks->Generate Scripts See the image here

Then, Select the table or tables which you need insert script to be generated. see the image here

Now Click next and click on Advanced and select "Types of data to script" see the image here

Finally, next, next, you get all your data in insert scripts. Good bye excel!

Answer 2

Use data publishing wizard. step by step explanation at :
http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

Answer 3

Unfortunately the options to script data along with schema didn't seem to appear until SQL Server 2008, so I think you're stuck with third party options.

• SSMS Tool Pack has a generate inserts option
• Narayana Vyas Kondreddi has an excellent generate inserts script I've been using for a few years now

Answer 4

I had the same problem before, and there seems to be no direct script can be made to take all tables structures with their data.

I came around this situation by doing the following:

1. Generate a script for database architecture (RightClick on Database > Tasks > Generate Scripts > Select all tables, and script to a file)
2. Create a backup file for the database (RightClick on Database > Tasks > Backup)

Then, to take your database somewhere else:

1. Execute the database script file to create tables
2. Restore the database using [Database Name] > Restore > Database.

This worked just fine for me using SQL Server 2005.