如何将表中的所有数据导出为可插入的 sql 格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20542819/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export all data from table to an insertable sql format?
提问by victorio
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.
我在 Microsoft SQL Server Management StudioA_table的数据库（称之为A_db）中有一个表（称之为），有 10 行。
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_tablehas. But the B_tableis empty.
我有另一个数据库（称之为B_db），它有一个表（称之为B_table），它具有与之前相同的列设置A_table。但是B_table是空的。
What I want:
我想要的是：
- Copyevery rows from
A_tabletoB_table.
- 将每一行从复制
A_table到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?
Microsoft SQL Server Management Studio 2012 中是否有任何选项可以从表中创建插入 SQL？或者有没有其他选择可以做到这一点？
回答by Armand
Quick and Easy way:
快速简便的方法：
- Right click database
- Point to
tasksIn SSMS 2017 you need to ignore step 2 - the generate scripts options is at the top level of the context menuThanks to Danielfor the comment to update. - Select
generate scripts - Click next
- Choose tables
- Click next
- Click advanced
- Scroll to
Types of data to script- Calledtypes of data to scriptin SMSS 2014 Thanks to Ellesedilfor commenting - Select
data only - Click on 'Ok' to close the advanced script options window
- Click next and generate your script
- 右键单击数据库
- 指向
tasksIn SSMS 2017 you need to ignore step 2 - the generate scripts options is at the top level of the context menu感谢Daniel的评论更新。 - 选择
generate scripts - 点击下一步
- 选择表格
- 点击下一步
- 点击高级
- 滚动至
Types of data to script-types of data to script在 SMSS 2014 中调用感谢Ellesedil的评论 - 选择
data only - 单击“确定”关闭高级脚本选项窗口
- 单击下一步并生成您的脚本
I usually in cases like this generate to a new query editor window and then just do any modifications where needed.
我通常在这种情况下生成一个新的查询编辑器窗口，然后在需要的地方进行任何修改。
回答by U.Y.
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).
我知道这是一个老问题，但 victorio 还询问是否还有其他选项可以将数据从一个表复制到另一个表。有一种非常简短且快速的方法可以将一个表中的所有记录插入到另一个表中（可能有也可能没有类似的设计）。
If you dont have identity column in table B_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.
如果表 B_table 中有标识列，则必须指定要插入的列。基本上你选择所有除了标识列，默认情况下会自动增加。
In case if you dont have existing B_table in B_db
如果您在 B_db 中没有现有的 B_table
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
将使用所有现有值在数据库 B_db 中创建表 B_table
回答by oleg
Another way to dump data as file from table by DumpDataFromTable sproc
通过 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
注意：SQL必须有创建文件的权限，如果没有设置则exec follow line一次
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
通过这个脚本，您可以调用 sproc: DumpDataFromTable.sql 并一次性转储更多表，而不是从 Management Studio 手动一张一张
By default the format of generated scrip will be like
默认情况下，生成的脚本的格式将类似于
INSERT INTO <TableName> SELECT <Values>
Or you can change the generated format into
或者您可以将生成的格式更改为
SELECT ... FROM
by setting variable @BuildMethod = 2
通过设置变量@BuildMethod = 2
full sproc code:
完整的 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
Or can be downloaded latest version from https://github.com/Zindur/MSSQL-DumpTable/tree/master/Scripts
或者可以从https://github.com/Zindur/MSSQL-DumpTable/tree/master/Scripts下载最新版本
回答by Mants
回答by Alok Singh
We just need to use below query to dump one table data into other table.
我们只需要使用下面的查询将一个表数据转储到另一个表中。
Select * into SampleProductTracking_tableDump
from SampleProductTracking;
SampleProductTracking_tableDumpis a new table which will be created automatically
when using with above query.
It will copy the records from SampleProductTrackingto SampleProductTracking_tableDump
SampleProductTracking_tableDump是一个新表，将在使用上述查询时自动创建。它将记录从SampleProductTracking到SampleProductTracking_tableDump
回答by CRAFTY DBA
I have not seen any option in Microsoft SQL Server Management Studio 2012 to-date that will do that.
迄今为止，我还没有在 Microsoft SQL Server Management Studio 2012 中看到任何可以做到这一点的选项。
I am sure you can write something in T-SQL given the time.
我相信你可以在时间允许的情况下用 T-SQL 写一些东西。
Check out TOAD from QUEST- now owned by DELL.
查看来自 QUEST 的 TOAD- 现在归戴尔所有。
http://www.toadworld.com/products/toad-for-oracle/f/10/t/9778.aspx
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”
Nice thing about toad, it works with both SQL server and Oracle. If you have to work with both, it is a good investment.
蟾蜍的好东西，它适用于 SQL 服务器和 Oracle。如果您必须与两者合作，这是一项不错的投资。
