到目前为止，我还没有在Microsoft SQL Server Management Studio 2012中看到任何选项会这样做。

我相信只要有时间，您可以用T-SQL编写一些东西。

检查蛤蟆从任务-现在由戴尔拥有。

http://www.toadworld.com/products/toad-for-oracle/f/10/t/9778.aspx

Select your rows.
Rt -click -> Export Dataset.
Choose Insert Statement format
Be sure to check “selected rows only”

toad的优点是，它可以同时使用SQL server和Oracle。如果你必须兼顾两者，这是一项很好的投资。

简单快捷的方法:

右击数据库 在SSMS 2017中，您需要忽略第2步-生成脚本选项位于上下文菜单的顶层。 选择生成脚本 单击next 选择表 单击next 单击高级 滚动到脚本的数据类型-在SMSS 2014中调用脚本的数据类型感谢Ellesedil的评论 只选择数据 点击“确定”关闭高级脚本选项窗口 单击下一步并生成脚本

我通常在这种情况下生成一个新的查询编辑器窗口，然后在需要的地方做任何修改。

我知道这是一个老问题，但victorio还问是否有其他选项可以将数据从一个表复制到另一个表。有一种非常短且快速的方法可以将所有记录从一个表插入到另一个表(它们的设计可能相似，也可能不同)。

如果在表B_table中没有标识列:

INSERT INTO A_db.dbo.A_table
SELECT * FROM B_db.dbo.B_table

如果在表B_table中有标识列，则必须指定要插入的列。基本上，您可以选择除标识列之外的所有列，默认情况下，标识列将自动递增。

如果你没有现有的B_table在B_db

SELECT *
INTO B_db.dbo.B_table
FROM A_db.dbo.A_table

将创建表B_table在数据库B_db与所有现有的值

通过DumpDataFromTable sproc将数据转储为文件的另一种方法

EXEC dbo.DumpDataFromTable
     @SchemaName = 'dbo'
    ,@TableName = 'YourTableName'
    ,@PathOut = N'c:\tmp\scripts\' -- folder must exist !!!'

注意:SQL必须有创建文件的权限，如果没有设置，则exec跟随行一次

EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;

通过这个脚本，您可以调用sproc: DumpDataFromTable。sql和转储更多的表，而不是手动一个接一个地从Management Studio

默认情况下，生成的脚本的格式如下

INSERT INTO <TableName> SELECT <Values>

或者您可以将生成的格式更改为

SELECT ... FROM

设置变量@BuildMethod = 2

完整的sproc代码:

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 <> 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 <> 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 <> 0 BEGIN
                PRINT ''OpenTextFile''
                GOTO Error_Handler
            END            
        END

        EXECUTE @OLEResult = sp_OAMethod @FileID, ''WriteLine'', NULL, @FileBody
        IF @OLEResult <> 0 BEGIN
            PRINT ''WriteLine''
            GOTO Error_Handler
        END     

        EXECUTE @OLEResult = sp_OAMethod @FileID,''Close''
        IF @OLEResult <> 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) > 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 <> @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 <> @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

或可从https://github.com/Zindur/MSSQL-DumpTable/tree/master/Scripts下载最新版本

命令从linux机器终端获取数据库备份。

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"

在Linux操作系统下备份和恢复SQL Server数据库

我们只需要使用下面的查询将一个表的数据转储到另一个表。

Select * into SampleProductTracking_tableDump
from SampleProductTracking;

SampleProductTracking_tableDump是一个将自动创建的新表 当使用与上述查询。 它将记录从SampleProductTracking复制到SampleProductTracking_tableDump

我写了这个T-SQL代码，它允许生成一个导入脚本(使用自sql server 2016以来可用的压缩/解压缩功能) 这个脚本可以导出为xml, xml转换为varbinay，这个varbinay压缩。

declare @tablename  nvarchar(255) = 'dbo.toto'

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------

set nocount on

declare @codetab        nvarchar(255) = replace(replace(replace(replace(@tablename,' ','_'), '.','_'),'[', ''),']', '')
declare @nl             nvarchar(2)= char(10) -- + char(13)
declare @tab            nvarchar(1)= char(9)
declare @export         nvarchar(max)
declare @exportcompress nvarchar(max)
declare @sqlgenxml      nvarchar(max)
declare @sqlimport      nvarchar(max) 
declare @sqlstruct      nvarchar(max)
declare @sqlinsert      nvarchar(max)
declare @nbcarmax       int
declare @index          int = 0
declare @cc             int = 0
declare @maxsize        int = 1024

declare @struct table (
    name               nvarchar(255),
    codename           nvarchar(255),
    col_id             int,
    is_nullable        int,
    is_identity_column int,
    is_updateable      int,
    type               nvarchar(255)
)

insert into @struct (name, col_id, type, is_nullable, is_identity_column, is_updateable) 
select
    name,
    column_ordinal,
    system_type_name,
    is_nullable,
    is_identity_column,
    is_updateable
from
    sys.dm_exec_describe_first_result_set('select * from '+@tablename, NULL, 0)

update @struct set codename = replace(replace(replace(replace(name,' ','_'), '.','_'),'[', ''),']', '')

select @nbcarmax=max(len(codename)) from @struct
select @sqlgenxml = coalesce(@sqlgenxml + @tab + ',', @tab + ' ')+ name + replicate(' ', @nbcarmax-len(codename)+3)+' as ' +QUOTENAME(codename)+@nl from @struct order by col_id
select @sqlstruct = coalesce(@sqlstruct + @tab + ',', @tab + ' ')+ quotename(name) + replicate(' ', @nbcarmax-len(codename)+3)+type+case when is_identity_column=1 then ' identity(1,1)' else '' end +@nl from @struct order by col_id
select @sqlinsert = coalesce(@sqlinsert + '--'+ @tab + ',', '--'+@tab + ' ')+ quotename(name) + @nl from @struct order by col_id

set @sqlgenxml = 'set @s =(select'+@nl+@sqlgenxml+'from'+@tab+@tablename+@nl+'for xml path(''row''), root('''+@codetab+'''))'+@nl

exec sp_executesql @sqlgenxml, N'@s nvarchar(max) output', @s=@export output

select @exportcompress = convert(nvarchar(max), COMPRESS(cast(@export as varbinary(max))), 1)


print 'set nocount on'+@nl+@nl
    + '/*'+@nl
    + 'create table '+@tablename+' ('+@nl
    + @sqlstruct 
    + ')' + @nl
    + '*/'+@nl + @nl
    +@nl
    +'declare @import nvarchar(max) ='''''+@nl
    +'declare @xml xml'+@nl
    +@nl
    +'declare @importtab table ('+@nl
    +@tab+'id  int identity(1,1),'+@nl
    +@tab+'row nvarchar(max)'+@nl
    +')'+@nl
    +@nl

while @index<LEN(@exportcompress)
begin
    set @cc+=1
    print 'insert into @importtab (row) values ('''+SUBSTRING(@exportcompress, @index, @maxsize)+''') --'+CAST(@cc as varchar(10))
    set @index+=@maxsize
end

print @nl
    +'select @import += row from @importtab order by id'+@nl
    +'select @import = cast(decompress(convert(varbinary(max), @import, 1)) as nvarchar(max))'+@nl
    + @nl
    +'set @xml=cast(@import as xml)'+@nl
    + @nl

select 
    @sqlimport = 
          coalesce(@sqlimport+@tab+',',@tab+' ')
        + 't.row.value(''./'+codename+'[1]'','
        + replicate(' ', @nbcarmax-len(codename)+3)
        + ''''+type+''''
        + replicate(' ', 20-len(type))
        + ') as '+QUOTENAME(name) 
        +  @nl 
from 
    @struct
set @sqlimport='select'+@nl+@sqlimport+'from'+@nl+@tab+'@xml.nodes(''/'+@codetab+'/row'') as t(row)'

print '-- truncate table '+@tablename+@nl

if exists(select top 1 1 from @struct where is_identity_column = 1)
print '-- set identity_insert '+@tablename+' on'+@nl

print '-- insert into '+@tablename+' ('+@nl+@sqlinsert+'-- )'+@nl+@sqlimport+@nl

if exists(select top 1 1 from @struct where is_identity_column = 1)
print '-- set identity_insert '+@tablename+' off'+@nl

下面您可以看到这个脚本生成的示例(从SSMS结果文本复制/粘贴)

set nocount on

/*
create table dbo.toto (
     [id]           int identity(1,1)
    ,[code]         nvarchar(10)
    ,[value]        numeric(18,9)
    ,[creationdt]   datetime
)
*/


declare @import nvarchar(max) =''
declare @xml xml

declare @importtab table (
    id  int identity(1,1),
    row nvarchar(max)
)


insert into @importtab (row) values ('0x1F8B0800000000000400E4DDD16E246B9A5EE...DE52') --1
...
insert into @importtab (row) values ('890639F9...69A8C486F8405') --3140

select @import += row from @importtab order by id
select @import = cast(decompress(convert(varbinary(max), @import, 1)) as nvarchar(max))

set @xml=cast(@import as xml)


-- truncate table dbo.toto

-- set identity_insert dbo.toto on

-- insert into dbo.toto (
--   [id]
--  ,[code]
--  ,[value]
--  ,[creationdt]
-- )
select
     t.row.value('./id[1]',           'int'                 ) as [id]
    ,t.row.value('./code[1]',         'nvarchar(10)'        ) as [code]
    ,t.row.value('./value[1]',        'numeric(18,9)'       ) as [value]
    ,t.row.value('./creationdt[1]',   'datetime'            ) as [creationdt]
from
    @xml.nodes('/dbo_toto/row') as t(row)

-- set identity_insert dbo.toto off

你会发现有用的