从 SQL Server 导出表以导入到 Oracle 10g
我正在尝试从 SQL Server 2005 导出一些表，然后创建这些表并将其填充到 Oracle 中。
我有大约 10 个表，从 4 列到 25 列不等。我没有使用任何约束/键，所以这应该是相当简单的。
首先，我生成脚本来获取表结构，然后修改它们以符合 Oracle 语法标准（即将 nvarchar 更改为 varchar2）。
接下来，我使用 SQL Server 导出向导导出数据，该向导创建了一个 csv 平面文件。然而我的主要问题是我找不到强制 SQL Server 双引号列名的方法。我的其中一列包含逗号，因此除非我能找到一种让 SQL Server 引用列名的方法，否则在导入它时我会遇到麻烦。
另外，我是走困难的路线，还是有更简单的方法来做到这一点？
谢谢
编辑：通过引用，我指的是引用 csv 中的列值。例如，我有一列包含类似地址
101 High Street，某镇，Some 县，PO5TC053
如果不更改为以下内容，加载 CSV 时会出现问题
“101 High Street，某镇，某 县，PO5TC053"
I'm trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle.
I have about 10 tables, varying from 4 columns up to 25. I'm not using any constraints/keys so this should be reasonably straight forward.
Firstly I generated scripts to get the table structure, then modified them to conform to Oracle syntax standards (ie changed the nvarchar to varchar2)
Next I exported the data using SQL Servers export wizard which created a csv flat file. However my main issue is that I can't find a way to force SQL Server to double quote column names. One of my columns contains commas, so unless I can find a method for SQL server to quote column names then I will have trouble when it comes to importing this.
Also, am I going the difficult route, or is there an easier way to do this?
Thanks
EDIT: By quoting I'm refering to quoting the column values in the csv. For example I have a column which contains addresses like
101 High Street, Sometown, Some
county, PO5TC053
Without changing it to the following, it would cause issues when loading the CSV
"101 High Street, Sometown, Some
county, PO5TC053"
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用 SQLDeveloper 查看一些选项后，或者手动尝试导出/导入，我在 SQL Server Management Studio 上找到了一个实用程序，它可以获得所需的结果，并且易于使用，请执行以下操作
After looking at some options with SQLDeveloper, or to manually try to export/import, I found a utility on SQL Server management studio that gets the desired results, and is easy to use, do the following
使用 SQLDeveloper 迁移工具
我认为引用 Oracle 中的列名称是你不应该使用的东西。它会导致各种各样的问题。
Use the SQLDeveloper migration tools
I think quoting column names in oracle is something you should not use. It causes all sort of problems.
正如罗伯特所说，我强烈建议再次引用列名。结果是，您不仅在导入数据时必须引用它们，而且每当您想在 SQL 语句中引用该列时也必须引用它们 - 是的，这可能也意味着在您的程序代码中。构建 SQL 语句变得非常麻烦！
根据您所写的内容，我不确定您是否指的是列名称或这些列中的数据。 （SQLServer 真的可以在 name 列中包含逗号吗？如果有充分的理由，我会感到非常惊讶！）应该引用列内容任何类似字符串的列（尽管我发现其他字符通常效果更好，因为需要“转义”引号成为另一个问题）。如果您以 CSV 格式导出，这应该是一个选项..但是我不熟悉导出向导。
移动数据的另一个想法（取决于项目的规模）是使用 ETL/EAI 工具。我一直在尝试使用 Pentaho 套件及其 Kettle 组件。它提供了一系列将数据从一个地方移动到另一个地方的选项。对于简单的转移来说，它可能有点过大，但如果是具有相应体积的大“迁移”，它可能是一个不错的选择。
As Robert has said, I'd strongly advise agains quoting column names. The result is that you'd have to quote them not only when importing the data, but also whenever you want to reference that column in a SQL statement - and yes, that probably means in your program code as well. Building SQL statements becomes a total hassle!
From what you're writing, I'm not sure if you are referring to the column names or the data in these columns. (Can SQLServer really have a comma in the column name? I'd be really surprised if there was a good reason for that!) Quoting the column content should be done for any string-like columns (although I found that other characters usually work better as the need to "escape" quotes becomes another issue). If you're exporting in CSV that should be an option .. but then I'm not familiar with the export wizard.
Another idea for moving the data (depending on the scale of your project) would be to use an ETL/EAI tool. I've been playing around a bit with the Pentaho suite and their Kettle component. It offered a good range of options to move data from one place to another. It may be a bit oversized for a simple transfer, but if it's a big "migration" with the corresponding volume, it may be a good option.