I have a SQL Server 2005 instance, into which I am trying to import data from a SQL Server 2008 instance using an SQL Query. I am using the 2008 management studio, and the import/export data wizard.
我有一个SQL Server 2005实例，我试图使用SQL查询从SQL Server 2008实例导入数据。我正在使用2008 management studio和import/export数据向导。
If I run the select query separately in the management studio, it correctly returns the ~88k rows that are required. The query returns the data with the exact column names and types required by the destination table.
如果我在management studio中单独运行select查询，它会正确地返回所需的~88k行。查询将返回目标表所需的精确列名称和类型的数据。
When I run the import wizard, the sql query parses correctly, and the 'Preview' button correctly shows the data. There are no errors or warnings in the conversion section. The task is set to fail if there are any failures in conversion.
当我运行导入向导时，sql查询解析正确，“预览”按钮正确显示数据。在转换部分中没有错误或警告。如果在转换过程中出现任何失败，任务将被设置为失败。
When I run the task, no errors are displayed. However, it shows '0 rows transferred' and no data is imported.
当我运行任务时，不会显示错误。但是，它显示了“0行传输”，没有导入数据。
Any ideas why?
任何想法为什么?
edit: tried importing to a table created on import in a fresh new db, and still the same result. I'm wondering if the direction of movement from 2008 to 2005 is important (i.e. 2005 can't handle a 2008 feed correctly).
编辑:尝试导入在导入新db时创建的表，但结果仍然相同。我想知道从2008年到2005年的移动方向是否重要(例如，2005不能正确地处理2008年的提要)。
4 个解决方案
#1
2
If you have "USE [database]" as part of your query, then this single line is all that gets executed during the import/export.
如果您将“USE [database]”作为查询的一部分，那么这一行就是在导入/导出期间执行的所有内容。
The solution is to remove the "USE" statement as part of your query.
解决方案是删除“使用”语句作为查询的一部分。
#2
1
I've never had much luck with the SQL server management studio's import features under 2005/8.
我对SQL server management studio在2005/8年的导入特性从来没有什么好感。
Lately, I do one of two things.
最近，我做了两件事中的一件。
Either I just use it to import the data into a brand new table on the target server (not even a table of the exact same structure) then run an insert statement to transfer from that newly created table into the destination.
要么使用它将数据导入到目标服务器上的一个全新的表中(甚至不是完全相同结构的表)，然后运行一条insert语句，从新创建的表传输到目标服务器。
Or I use a tool like Visual Studio for Database Professional (or Redgate) to transfer the data.
或者我使用Visual Studio作为数据库专业人员(或Redgate)来传输数据。
#3
1
I've used the import successfully. Chris has a good suggestion (this is the process I typically follow) though on using a middle table or file. That gives you the ability to do some simple transforms using queries rather than SQL's transform tool. It also gives you a buffer in case things go wrong or you need to isolate issues.
我已经成功地使用了导入。Chris有一个很好的建议(这是我通常会遵循的过程)，尽管使用的是中间表或文件。这使您能够使用查询而不是SQL的转换工具进行一些简单的转换。它还为您提供一个缓冲区，以防出现问题或需要隔离问题。
#4
1
I had a similar problem using the import utility for 2008. We had Oracle native SQL that ran fine in TOAD but imported with 0 rows using the SQL Server import utility. The culprit ended up being related to Oracle's date format. The SQL referred to dates such as '03-JUN-2013'. Once I changed them to use the TO_DATE function such as TO_DATE('06/03/2013','MM/DD/YYYY') we had a successfully execution and import.
我在2008年使用import工具时遇到了类似的问题。我们有Oracle native SQL，它在TOAD中运行得很好，但是使用SQL Server import实用程序以0行导入。罪魁祸首与Oracle的日期格式有关。SQL指的是“03-JUN-2013”等日期。一旦我将它们更改为使用TO_DATE函数，比如TO_DATE('06/03/2013'，'MM/DD/ yyyyy ')，我们就成功地执行和导入了。
#1
2
If you have "USE [database]" as part of your query, then this single line is all that gets executed during the import/export.
如果您将“USE [database]”作为查询的一部分，那么这一行就是在导入/导出期间执行的所有内容。
The solution is to remove the "USE" statement as part of your query.
解决方案是删除“使用”语句作为查询的一部分。
#2
1
I've never had much luck with the SQL server management studio's import features under 2005/8.
我对SQL server management studio在2005/8年的导入特性从来没有什么好感。
Lately, I do one of two things.
最近，我做了两件事中的一件。
Either I just use it to import the data into a brand new table on the target server (not even a table of the exact same structure) then run an insert statement to transfer from that newly created table into the destination.
要么使用它将数据导入到目标服务器上的一个全新的表中(甚至不是完全相同结构的表)，然后运行一条insert语句，从新创建的表传输到目标服务器。
Or I use a tool like Visual Studio for Database Professional (or Redgate) to transfer the data.
或者我使用Visual Studio作为数据库专业人员(或Redgate)来传输数据。
#3
1
I've used the import successfully. Chris has a good suggestion (this is the process I typically follow) though on using a middle table or file. That gives you the ability to do some simple transforms using queries rather than SQL's transform tool. It also gives you a buffer in case things go wrong or you need to isolate issues.
我已经成功地使用了导入。Chris有一个很好的建议(这是我通常会遵循的过程)，尽管使用的是中间表或文件。这使您能够使用查询而不是SQL的转换工具进行一些简单的转换。它还为您提供一个缓冲区，以防出现问题或需要隔离问题。
#4
1
I had a similar problem using the import utility for 2008. We had Oracle native SQL that ran fine in TOAD but imported with 0 rows using the SQL Server import utility. The culprit ended up being related to Oracle's date format. The SQL referred to dates such as '03-JUN-2013'. Once I changed them to use the TO_DATE function such as TO_DATE('06/03/2013','MM/DD/YYYY') we had a successfully execution and import.
我在2008年使用import工具时遇到了类似的问题。我们有Oracle native SQL，它在TOAD中运行得很好，但是使用SQL Server import实用程序以0行导入。罪魁祸首与Oracle的日期格式有关。SQL指的是“03-JUN-2013”等日期。一旦我将它们更改为使用TO_DATE函数，比如TO_DATE('06/03/2013'，'MM/DD/ yyyyy ')，我们就成功地执行和导入了。