SQL Server Data Import Wizard using query from another server

Question

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.

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

Answer 1

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.

Answer 2

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.