SQL Server Data Import Wizard using query from another server

I have a SQL Server 2005 instance, into which I am trying to import data from a SQL Se开发者_JS百科rver 2008 instance using an SQL Query. I am using the 2008 management studio, and the import/export data wizard.

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.

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.

When I run the task, no errors are displayed. However, it shows '0 rows transferred' and no data is imported.

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).

If you have "USE [database]" as part of your query, then this single line is all that gets executed during the import/export.

The solution is to remove the "USE" statement as part of your query.

I've never had much luck with the SQL server management studio's import features under 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.

Or I use a tool like Visual Studio for Database Professional (or Redgate) to transfer the data.

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.

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.