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

Question

I have a Table  call it A table  in a database  call it A db  in Microsoft SQL Server Management Studio  and there are 10 rows   I have another database  call it B db   and it has a Table  call it B table   which has the same column settings as A table has  But the B table is empty   What I want    Copy every rows from A table to B table    Is there any option in Microsoft SQL Server Management Studio 2012  to create an insert SQL from a table  Or is there any other option to do that

User · Answer

Command to get the database backup from linux machine terminal.

sqlcmd -S localhost -U SA -Q "BACKUP DATABASE [demodb] TO DISK = N'/var/opt/mssql/data/demodb.bak' WITH NOFORMAT, NOINIT, NAME = 'demodb-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

Backup and restore SQL Server databases on Linux

User · Answer

I have not seen any option in Microsoft SQL Server Management Studio 2012 to-date that will do that   I am sure you can write something in T-SQL given the time   Check out TOAD from QUEST - now owned by DELL   http   www toadworld com products toad-for-oracle f 10 t 9778 aspx  Select your rows  Rt -click - gt  Export Dataset  Choose Insert Statement format Be sure to check    selected rows only      Nice thing about toad  it works with both SQL server and Oracle   If you have to work with both  it is a good investment

User · Answer

Another way to dump data as file from table by DumpDataFromTable sproc  EXEC dbo DumpDataFromTable       SchemaName    dbo        TableName    YourTableName        PathOut   N c  tmp scripts   -- folder must exist        Note  SQL must have permission to create files  if is not set-up then exec follow line once  EXEC sp configure  Ole Automation Procedures   1  RECONFIGURE WITH OVERRIDE    By this script you can call the sproc  DumpDataFromTable sql and dump more tables in one go  instead of doing manually one by one from Management Studio  By default the format of generated scrip will be like   INSERT INTO  lt TableName gt  SELECT  lt Values gt    Or you can change the generated format into   SELECT     FROM   by setting variable  BuildMethod   2  full sproc code     IF EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   DumpDataFromTable    AND type in  N P   N PC        DROP PROCEDURE dbo  DumpDataFromTable  GO  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  --                                               -- Author     Oleg Ciobanu -- Create date  20171214 -- Version 1 02 -- Description  -- dump data in 2 formats --  BuildMethod   1 INSERT INTO format --  BuildMethod   2 SELECT   FROM format -- -- SQL must have permission to create files  if is not set-up then exec follow line once -- EXEC sp configure  Ole Automation Procedures   1  RECONFIGURE WITH OVERRIDE  -- --                                               CREATE PROCEDURE  dbo   DumpDataFromTable          SchemaName nvarchar 128  --   dbo        TableName nvarchar 128  --   testTable        WhereClause nvarchar  1000       -- must start with AND       BuildMethod int   1 -- taking values 1 for INSERT INTO forrmat or 2 for SELECT from value Table       PathOut nvarchar 250    N c  tmp scripts   -- folder must exist            AsFileNAme nvarchar 250    NULL -- if is passed then will use this value as FileName       DebugMode int   0   AS BEGIN       SET NOCOUNT ON           -- run follow next line if you get permission deny  for sp OACreate sp OAMethod         -- EXEC sp configure  Ole Automation Procedures   1  RECONFIGURE WITH OVERRIDE       DECLARE  Sql nvarchar  max      DECLARE  SqlInsert nvarchar  max           DECLARE  Columns nvarchar max      DECLARE  ColumnsCast nvarchar max       -- cleanUp prepraring data     SET  SchemaName   REPLACE REPLACE  SchemaName                     SET  TableName   REPLACE REPLACE  TableName                     SET  AsFileNAme   NULLIF  AsFileNAme         SET  AsFileNAme   REPLACE  AsFileNAme              SET  AsFileNAme   COALESCE  PathOut    AsFileNAme     sql    PathOut    SchemaName   ISNULL        TableName N        sql         --debug     IF  DebugMode   1         PRINT  AsFileNAme          -- Create temp SP what will be responsable for generating script files     DECLARE  PRC WritereadFile VARCHAR max             IF EXISTS  SELECT   FROM sys objects WHERE type     P   AND name     PRC WritereadFile           BEGIN           DROP  Procedure  PRC WritereadFile        END       EXEC    PRC WritereadFile         --         SET  PRC WritereadFile        CREATE Procedure PRC WritereadFile            FileMode INT -- Recreate   0 or Append Mode 1          Path NVARCHAR 1000           AsFileNAme NVARCHAR 500           FileBody NVARCHAR MAX                  AS         DECLARE  OLEResult INT         DECLARE  FS INT         DECLARE  FileID INT         DECLARE  hr INT         DECLARE  FullFileName NVARCHAR 1500     Path    AsFileNAme          -- Create Object         EXECUTE  OLEResult   sp OACreate   Scripting FileSystemObject     FS OUTPUT         IF  OLEResult  lt  gt  0 BEGIN             PRINT   Scripting FileSystemObject               GOTO Error Handler         END              IF  FileMode   0 BEGIN  -- Create             EXECUTE  OLEResult   sp OAMethod  FS   CreateTextFile    FileID OUTPUT   FullFileName             IF  OLEResult  lt  gt  0 BEGIN                 PRINT   CreateTextFile                   GOTO Error Handler             END         END ELSE BEGIN          -- Append             EXECUTE  OLEResult   sp OAMethod  FS   OpenTextFile    FileID OUTPUT   FullFileName  8  0 -- 8- forappending             IF  OLEResult  lt  gt  0 BEGIN                 PRINT   OpenTextFile                   GOTO Error Handler             END                     END          EXECUTE  OLEResult   sp OAMethod  FileID    WriteLine    NULL   FileBody         IF  OLEResult  lt  gt  0 BEGIN             PRINT   WriteLine               GOTO Error Handler         END               EXECUTE  OLEResult   sp OAMethod  FileID   Close           IF  OLEResult  lt  gt  0 BEGIN             PRINT   Close               GOTO Error Handler         END          EXECUTE sp OADestroy  FS         EXECUTE sp OADestroy  FileID          GOTO Done          Error Handler              DECLARE  source varchar 30    desc varchar  200                     EXEC  hr   sp OAGetErrorInfo null   source OUT   desc OUT             PRINT       ERROR                   SELECT OLEResult    OLEResult  hr   CONVERT  binary 4    hr   source    source  description    desc         Done                 --       EXEC    PRC WritereadFile       EXEC PRC WritereadFile 0   Create         AsFileNAme            WITH steColumns AS           SELECT             1 as rn              c ORDINAL POSITION              c COLUMN NAME as ColumnName              c DATA TYPE as ColumnType         FROM INFORMATION SCHEMA COLUMNS c         WHERE 1   1         AND c TABLE SCHEMA    SchemaName         AND c TABLE NAME    TableName            --SELECT           SELECT              ColumnsCast     SELECT                                     CASE WHEN ColumnType IN   date   time   datetime2   datetimeoffset   smalldatetime   datetime   timestamp                                           THEN                                              convert nvarchar 1001   s      ColumnName             121  AS      ColumnName                                                    -- convert nvarchar   DateTimeScriptApplied   121  as  DateTimeScriptApplied                                          ELSE                                              CAST s      ColumnName           AS NVARCHAR 1001   AS      ColumnName                                            END                                      as  data                                                                          FROM                                       steColumns t2                                     WHERE 1  1                                       AND t1 rn   t2 rn                                     FOR xml PATH                                                        Columns     SELECT                                           ColumnName        as  data                                                                          FROM                                       steColumns t2                                     WHERE 1  1                                       AND t1 rn   t2 rn                                     FOR xml PATH                                               FROM steColumns t1      -- remove last char     IF lEN  Columns   gt  0 BEGIN         SET  Columns   SUBSTRING  Columns  1  LEN  Columns -1           SET  ColumnsCast   SUBSTRING  ColumnsCast  1  LEN  ColumnsCast -1       END      -- debug     IF  DebugMode   1 BEGIN         print  ColumnsCast         print  Columns         select  ColumnsCast     Columns     END      -- build unpivoted Data     SET  SQL         SELECT         u rn           c ORDINAL POSITION as ColumnPosition           c DATA TYPE as ColumnType           u ColumnName           u ColumnValue     FROM      SELECT         ROW NUMBER   OVER  ORDER BY  SELECT NULL   AS rn              CHAR 13     ColumnsCast       CHAR 13     FROM       SchemaName            TableName      s        CHAR 13     WHERE 1   1        CHAR 13    COALESCE  WhereClause           CHAR 13       tt     UNPIVOT             ColumnValue       FOR ColumnName in           CHAR 13     Columns       CHAR 13                       u      LEFT JOIN INFORMATION SCHEMA COLUMNS c ON c COLUMN NAME   u ColumnName         AND c TABLE SCHEMA         SchemaName               AND c TABLE NAME          TableName          ORDER BY u rn               c ORDINAL POSITION            -- debug     IF  DebugMode   1 BEGIN         print  Sql              exec   Sql      END      -- prepare data for cursor      IF OBJECT ID  tempdb   tmp   IS NOT NULL         DROP TABLE  tmp     CREATE TABLE  tmp               rn bigint          ColumnPosition int          ColumnType varchar  128           ColumnName varchar  128           ColumnValue nvarchar  2000  -- I hope this size will be enough for storring values           SET  Sql    INSERT INTO   tmp     CHAR 13      Sql      -- debug     IF  DebugMode   1 BEGIN         print  Sql     END      EXEC   Sql    -- Insert dummy rec  otherwise will not proceed the last rec    INSERT INTO  tmp  rn  SELECT MAX rn     1  FROM  tmp         IF  DebugMode   1 BEGIN         SELECT   FROM  tmp     END      DECLARE  rn bigint           ColumnPosition int           ColumnType varchar  128            ColumnName varchar  128            ColumnValue nvarchar  2000            i int   -1 -- counter flag           ColumnsInsert varchar max    NULL           ValuesInsert nvarchar max    NULL      DECLARE cur CURSOR FOR     SELECT rn  ColumnPosition  ColumnType  ColumnName  ColumnValue     FROM  tmp     ORDER BY rn  ColumnPosition -- note order is really important         OPEN cur      FETCH NEXT FROM cur     INTO  rn   ColumnPosition   ColumnType   ColumnName   ColumnValue      IF  BuildMethod   1     BEGIN         SET  SqlInsert    SET NOCOUNT ON     CHAR 13           EXEC PRC WritereadFile 1   Add         AsFileName   SqlInsert         SET  SqlInsert          END     ELSE BEGIN         SET  SqlInsert    SET NOCOUNT ON     CHAR 13           SET  SqlInsert    SqlInsert                            SELECT                              CHAR 13     FROM                              CHAR 13     VALUES          EXEC PRC WritereadFile 1   Add         AsFileName   SqlInsert         SET  SqlInsert   NULL     END      SET  i    rn      WHILE   FETCH STATUS   0     BEGIN          IF   i  lt  gt   rn  -- is a new row         BEGIN             IF  BuildMethod   1             -- build as INSERT INTO -- as Default             BEGIN                 SET  SqlInsert    INSERT INTO       SchemaName            TableName                                           CHAR 13     ColumnsInsert                                         CHAR 13     VALUES                                       ValuesInsert                                   CHAR 13                     END             ELSE             BEGIN                 -- build as Table select                 IF   i  lt  gt   rn  -- is a new row                 BEGIN                     SET  SqlInsert   COALESCE  SqlInsert                     ValuesInsert                          EXEC PRC WritereadFile 1   Add         AsFileNAme   SqlInsert                     SET  SqlInsert      -- in method 2 we should clear script                 END                         END             -- debug             IF  DebugMode   1                 print  SqlInsert             EXEC PRC WritereadFile 1   Add         AsFileNAme   SqlInsert              -- we have new row             -- initialise variables             SET  i    rn             SET  ColumnsInsert   NULL             SET  ValuesInsert   NULL         END          -- build insert values         IF   i    rn  -- is same row         BEGIN             SET  ColumnsInsert   COALESCE  ColumnsInsert                    ColumnName                   SET  ValuesInsert    CASE                                                                   -- date                                     --WHEN                                     --   ColumnType IN   date   time   datetime2   datetimeoffset   smalldatetime   datetime   timestamp                                       --THEN                                     --  COALESCE  ValuesInsert                      ISNULL RTRIM  ColumnValue   NULL                                                -- numeric                                     WHEN                                          ColumnType IN   bit   tinyint   smallint   int   bigint                                                            money   real      float   decimal   numeric   smallmoney                                       THEN                                         COALESCE  ValuesInsert                  ISNULL RTRIM  ColumnValue   NULL                                            -- other types treat as string                                     ELSE                                         COALESCE  ValuesInsert                    ISNULL RTRIM                                                                                               -- escape single quote                                                                                             REPLACE  ColumnValue                                                                                                                  NULL                                                   END         END           FETCH NEXT FROM cur         INTO  rn   ColumnPosition   ColumnType   ColumnName   ColumnValue          -- debug         IF  DebugMode   1         BEGIN             print CAST  rn AS VARCHAR     -    CAST  ColumnPosition AS VARCHAR          END     END     CLOSE cur     DEALLOCATE cur      IF  BuildMethod   1     BEGIN         PRINT  ignore      END     ELSE BEGIN         SET  SqlInsert   CHAR 13       AS vtable                             CHAR 13            Columns                           CHAR 13                EXEC PRC WritereadFile 1   Add         AsFileNAme   SqlInsert         SET  SqlInsert   NULL     END     PRINT  Done       AsFileNAme END     Or can be downloaded latest version from https   github com Zindur MSSQL-DumpTable tree master Scripts

User · Answer

We just need to use below query to dump one table data into other table   Select   into SampleProductTracking tableDump from SampleProductTracking    SampleProductTracking tableDump is a new table which will be created automatically  when using with above query  It will copy the records from SampleProductTracking to SampleProductTracking tableDump

User · Answer

I know this is an old question  but victorio also asked if there are any other options to copy data from one table to another  There is a very short and fast way to insert all the records from one table to another  which might or might not have similar design    If you dont have identity column in table B table   INSERT INTO A db dbo A table SELECT   FROM B db dbo B table   If you have identity column in table B table  you have to specify columns to insert  Basically you select all except identity column  which will be auto incremented by default   In case if you dont have existing B table in B db  SELECT   INTO B db dbo B table FROM A db dbo A table   will create table B table in database B db with all existing values

User · Answer

Quick and Easy way    Right click database 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  Select generate scripts Click next Choose tables Click next Click advanced Scroll to Types of data to script - Called types of data to script in SMSS 2014 Thanks to Ellesedil for commenting Select data only Click on  Ok  to close the advanced script options window 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

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

Examples related to sql

Examples related to sql-server

Examples related to copy

Examples related to export

Examples related to ssms