How to Export Data from SQL Server 2008.2010 in Dml (Sql Script)

How to export data from SQL Server 2008.2010 in DML (SQL script)?

Here are the steps to generate the scripts from a database along with data. The screenshots were taken using SQL Server 2012 Management Studio but I believe that the steps are similar for SQL Server 2008 as well.

• Connect to the instance in which you would like to scrip the database along with data.
• Right-click on the database and select the option Tasks --> Generate Scripts...

• On the Generate and Publish Scripts wizard, click Next button.

• On the Choose objects step, I have left the default selection Script entire database and all database objects checked. You can also select the option Select specific database objects and pick the objects of your choice.

• On the Set Scripting Options step, select the path where you would like to save the script. Click on the Advanced button.

• On the Advanced Scripting Options dialog, scroll down to the option Types of data to script. By default, this is set to Schema only, which means it will generate only the create object statements. If you want both object creation script along with data, select the option Schema and data. Click OK on the Advanced Scripting Options dialog and click Next on the Set Scripting Options step

• On the Summary step, it will show all the options that you had selected. Click Next.

• On the Save or Publich Scripts step, if everything goes well you will see Success status appear against each object and the script should be saved to the location that you specified on the Set Scripting Options step.

Scripting the entire AdventureWorks database turned to be a huge 522 MB script file!

Hope that helps.

Generate a script of inserts from existing data with a select clause in SQL Server

I don't know if this can be done directly in SSMS, but you could create a query to return the content of the sql file (at least the insert statements):

SELECT 'insert into yourtable (id,othercol) values (' + cast(id as varchar) + ',''' + othercol + ''');'
FROM yourtable

SQL Fiddle Demo

Be sure to cast your non-varchar fields to varchar.

Exporting data In SQL Server as INSERT INTO

In SSMS in the Object Explorer, right click on the database, right-click and pick "Tasks" and then "Generate Scripts".

This will allow you to generate scripts for a single or all tables, and one of the options is "Script Data". If you set that to TRUE, the wizard will generate a script with INSERT INTO () statement for your data.

If using 2008 R2 or 2012 it is called something else, see screenshot below this one

2008 R2 or later eg 2012

Select "Types of Data to Script" which can be "Data Only", "Schema and Data" or "Schema Only" - the default).

And then there's a "SSMS Addin" Package on Codeplex (including source) which promises pretty much the same functionality and a few more (like quick find etc.)

How to export all data from table to an insertable sql format?

Quick and Easy way:

1. Right click database
2. Point to tasks In SSMS 2017 you need to ignore step 2 - the generate scripts options is at the top level of the context menu Thanks to Daniel for the comment to update.
3. Select generate scripts
4. Click next
5. Choose tables
6. Click next
7. Click advanced
8. Scroll to Types of data to script - Called types of data to script in SMSS 2014 Thanks to Ellesedil for commenting
9. Select data only
10. Click on 'Ok' to close the advanced script options window
11. Click next and generate your script

I usually in cases like this generate to a new query editor window and then just do any modifications where needed.

Command line approach to SQL script export of SQL Server database

Microsoft has a tool called mssql-scripter that does this. With it, you can do:

mssql-scripter -S localhost -d MyDB -U MyUser -P MyPassword --script-create > ./schema.sql

---

Related Topics