How to export table data into a SQL insert script?

Have you ever had a table that you wanted to drop and recreate but you need all the data inside it. Perhaps it took you a lot of time to generate this data.

Well, you can save the data out as a SQL insert script if you want. Here is how:

  1. Open Microsoft SQL Server Management Studio and login.
  2. Right-click on the database and choose Tasks | Generate Scripts.
    Note: The first time you do this you will see and Introduction page. Click Next. You can click the check box next to Do not show this page again and you won’t see this screen.
  3. On the Choose Objects screen, click Select specific database objects.
  4. Expand Tables and click the check box next to the table you want to save and click Next.
  5. On the Set Scripting Options screen, click Advanced.
  6. Scroll down and under Types of data to script select Data only and click OK.
  7. Under File name select a file name to save to and click Next.
  8. Click Next, Next, Finish.

You now have a script to insert your data.

2 Comments

  1. Rafael says:

    Urgh! That's why I prefer MySQL. Just mysqldump -h HOST -u user -p -database DATABASE > database.sql
    and voilá! There it is our sql (structure + data). Just one command instead of 8 steps and 8 windows 😛

Leave a Reply

How to post code in comments?