Exporting data In SQL Server as INSERT INTO

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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   gt  Get-DbaDbTable -SqlInstance sql2016 -Database MyDatabase              -Table  dbo Table1    dbo Table2             Export-DbaDbTableData -Path C  temp export sql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

for SQl server Mng Studio 2016

User · Answer

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

User · Answer

All the above is nice  but if you need to    Export data from multiple views and tables with joins Create insert statements for different RDBMSs 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

[sql] Exporting data In SQL Server as INSERT INTO

2008 R2 or later eg 2012

Examples related to sql

Examples related to sql-server

Examples related to insert

Examples related to data-migration