[sql] Exporting data In SQL Server as INSERT INTO

I am using SQL Server 2008 Management Studio and have a table I want to migrate to a different db server.

Is there any option to export the data as an insert into SQL script??

Here is an example of creating a data migration script using a cursor to iterate the source table.

SET NOCOUNT ON;  
DECLARE @out nvarchar(max) = ''
DECLARE @row nvarchar(1024)
DECLARE @first int = 1

DECLARE cur CURSOR FOR 
    SELECT '(' + CONVERT(CHAR(1),[Stage]) + ',''' + [Label] + ''')'
    FROM CV_ORDER_STATUS
    ORDER BY [Stage]

PRINT 'SET IDENTITY_INSERT dbo.CV_ORDER_STATUS ON'
PRINT 'GO'

PRINT 'INSERT INTO dbo.CV_ORDER_STATUS ([Stage],[Label]) VALUES';

OPEN cur
FETCH NEXT FROM cur
    INTO @row

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @first = 1
        SET @first = 0
    ELSE
        SET @out = @out + ',' + CHAR(13);

    SET @out = @out + @row

    FETCH NEXT FROM cur into @row
END

CLOSE cur
DEALLOCATE cur

PRINT @out

PRINT 'SET IDENTITY_INSERT dbo.CV_ORDER_STATUS OFF'
PRINT 'GO'

for SQl server Mng Studio 2016:

Just updating screenshots to help others as I am using a newer v18, circa 2019.

Here you can select certain tables or go with the default of all. For my own needs I'm indicating just the one table.

Next, there's the "Scripting Options" where you can choose output file, etc. As in multiple answers above (again, I'm just dusting off old answers for newer, v18.4 SQL Server Management Studio) what we're really wanting is under the "Advanced" button. For my own purposes, I need just the data.

Finally, there's a review summary before execution. After executing a report of operations' status is shown.

For those looking for a command-line version, Microsoft released mssql-scripter to do this:

$ pip install mssql-scripter

# Generate DDL scripts for all database objects and DML scripts (INSERT statements)
# for all tables in the Adventureworks database and save the script files in
# the current directory
$ mssql-scripter -S localhost -d AdventureWorks -U sa --schema-and-data \
                 -f './' --file-per-object

dbatools.io is a much more active project based on PowerShell, which provides the Get-DbaDbTable and Export-DbaDbTableData cmdlets to achieve this:

PS C:\> Get-DbaDbTable -SqlInstance sql2016 -Database MyDatabase \
           -Table 'dbo.Table1', 'dbo.Table2' | 
        Export-DbaDbTableData -Path C:\temp\export.sql

If you use it SQLServer 2008R2 you need to set Types of data to script field.

For the sake of over-explicit brainlessness, after following marc_s' instructions to here...

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

... I then see a wizard screen with "Introduction, Choose Objects, Set Scripting Options, Summary, and Save or Publish Scripts" with prev, next, finish, cancel buttons at the bottom.

On the Set Scripting Options step, you have to click "Advanced" to get the page with the options. Then, as Ghlouw has mentioned, you now select "Types of data to script" and profit.

You could also check out the "Data Scripter Add-In" for SQL Server Management Studio 2008 from:

http://www.mssql-vehicle-data.com/SSMS

Their features list:

• It was developed on SSMS 2008 and is not supported on the 2005 version at this time (soon!)

• Export data quickly to T-SQL for MSSQL and MySQL syntax

• CSV, TXT, XML are also supported! Harness the full potential, power, and speed that SQL has to offer.

• Don't wait for Access or Excel to do scripting work for you that could take several minutes to do -- let SQL Server do it for you and take all the guess work out of exporting your data!

• Customize your data output for rapid backups, DDL manipulation, and more...

• Change table names and database schemas to your needs, quickly and efficiently

• Export column names or simply generate data without the names.

• You can chose individual columns to script.

• You can chose sub-sets of data (WHERE clause).

• You can chose ordering of data (ORDER BY clause).

• Great backup utility for those grungy database debugging operations that require data manipulation. Don't lose data while experimenting. Manipulate data on the fly!

All the above is nice, but if you need to

1. Export data from multiple views and tables with joins
2. Create insert statements for different RDBMSs
3. Migrate data from any RDBMS to any RDBMS

then the following trick is the one and only way.

First learn how to create spool files or export result sets from the source db command line client. Second learn how to execute sql statements on the destination db.

Finally, create the insert statements (and any other statements) for the destination database by running an sql script on the source database. e.g.

SELECT '-- SET the correct schema' FROM dual;
SELECT 'USE test;' FROM dual;
SELECT '-- DROP TABLE IF EXISTS' FROM dual;
SELECT 'IF OBJECT_ID(''table3'', ''U'') IS NOT NULL DROP TABLE dbo.table3;' FROM dual;
SELECT '-- create the table' FROM dual;
SELECT 'CREATE TABLE table3 (column1 VARCHAR(10), column2 VARCHAR(10));' FROM dual;

SELECT 'INSERT INTO table3 (column1, column2) VALUES (''', table1.column1, ''',''', table2.column2, ''');' FROM table1 JOIN table2 ON table2.COLUMN1 = table1.COLUMN1;

The above example was created for Oracle's db where the use of dual is needed for table-less selects.

The result set will contain the script for the destination db.

After search a lot, it was my best shot:

If you have a lot of data and needs a compact and elegant script, try it: SSMS Tools Pack

It generates a union all select statements to insert items into target tables and handle transactions pretty well.

Screenshot

If you are running SQL Server 2008 R2 the built in options on to do this in SSMS as marc_s described above changed a bit. Instead of selecting Script data = true as shown in his diagram, there is now a new option called "Types of data to script" just above the "Table/View Options" grouping. Here you can select to script data only, schema and data or schema only. Works like a charm.