如何将整个SQL Server 2014数据库转储到文件中,以导入到Postgres数据库中? [英] How to dump an entire SQL Server 2014 database into a file, to be imported into a Postgres database?
本文介绍了如何将整个SQL Server 2014数据库转储到文件中,以导入到Postgres数据库中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个SQL Server 2014数据库,我只需要从中转储表数据(没有索引,存储过程或其他任何东西)。
I have a SQL Server 2014 database from which I need to dump just the table data (no indexes, stored procedures, or anything else).
此转储需要可以按原样导入Postgres 9.3数据库。
This dump needs to be imported into a Postgres 9.3 database "as-is".
创建此类转储的正确命令行是什么?
What id the proper command line to create such a dump?
推荐答案
我必须承认,这更像是个玩笑...您应该按照提示使用导出并将其写入某种CSV。只是为了好玩:
I must admit, this is more sort of a joke... You should rather follow the hint to use "Export" and write this to some kind of CSV. Just for fun:
列不能直接转换为 XML RAW
并添加无效数据:
columns, which are not directly convertible into XML RAW
are added with "invalid data":
DECLARE @Commands TABLE(ID INT IDENTITY,cmd NVARCHAR(MAX));
INSERT INTO @Commands(cmd)
SELECT '(SELECT TOP 3 '
+ STUFF(
(
SELECT ',' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_CATALOG=t.TABLE_CATALOG AND c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
AND c.DATA_TYPE NOT IN('image','text') AND c.DATA_TYPE NOT LIKE '%BINARY%'
FOR XML PATH('')
),1,1,''
)
+
(
SELECT ',''invalid data'' AS ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_CATALOG=t.TABLE_CATALOG AND c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
AND (c.DATA_TYPE IN('image','text') OR c.DATA_TYPE LIKE '%BINARY%')
FOR XML PATH('')
)
+ ' FROM ' + QUOTENAME(t.TABLE_CATALOG) + '.' + QUOTENAME(t.TABLE_SCHEMA) + '.'
+ QUOTENAME(t.TABLE_NAME) + ' FOR XML RAW,TYPE) AS ' + QUOTENAME(t.TABLE_CATALOG + '_' + t.TABLE_SCHEMA + '_' + t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES AS t WHERE t.TABLE_TYPE='BASE TABLE';
DECLARE @finalCommand NVARCHAR(MAX)=
(
SELECT 'SELECT '
+'(SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES FOR XML RAW,TYPE) AS ListOfTables'
+ (
SELECT ',' + cmd
FROM @Commands
ORDER BY ID
FOR XML PATH('')
)
+ ' FOR XML PATH(''AllTables'')'
);
EXEC( @finalCommand);
这篇关于如何将整个SQL Server 2014数据库转储到文件中,以导入到Postgres数据库中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文