How do I Export a SQL Server script?

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??

user2771704

5,7626 gold badges37 silver badges38 bronze badges

asked Oct 4, 2009 at 8:57

Jack KadaJack Kada

23.7k29 gold badges80 silver badges106 bronze badges

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.)

Eonasdan

7,3978 gold badges55 silver badges80 bronze badges

answered Oct 4, 2009 at 9:02

marc_smarc_s

715k172 gold badges1315 silver badges1434 bronze badges

11

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.

answered Oct 23, 2012 at 14:08

1

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

answered May 29, 2013 at 2:01

Azadeh KhojandiAzadeh Khojandi

3,6481 gold badge29 silver badges32 bronze badges

3

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.

answered Aug 2, 2011 at 8:09

GhlouwGhlouw

1,43016 silver badges19 bronze badges

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.

answered Apr 21, 2020 at 16:53

for SQl server Mng Studio 2016:

answered Jun 28, 2018 at 12:26

ingcontiingconti

10.4k3 gold badges62 silver badges45 bronze badges

1

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

answered Oct 17, 2017 at 0:08

NickolayNickolay

30.1k12 gold badges103 silver badges176 bronze badges

2

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!

Danica

27.9k6 gold badges88 silver badges118 bronze badges

answered Apr 22, 2012 at 1:51

0

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.

answered Jul 6, 2015 at 11:11

agelbessagelbess

4,1513 gold badges20 silver badges21 bronze badges

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'

answered Sep 9, 2016 at 20:23

Shane KenyonShane Kenyon

4,8072 gold badges38 silver badges36 bronze badges

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

answered Mar 22, 2018 at 14:29

How do I export SQL Server data script?

How do I export a table created script in SQL Server?

How do I export all table scripts in SQL Server?

How do I export a SQL statement?