SQL Standard 2005 Data Export Tool doesn't work

Question

Hi all,

I am facing a simple task of exporting data from source database to the target one, on the same SQL 2005 server. However this simple task proven to be not so simple in the 2005 edition.

I’ve started by using the built in export tool, which failed miserably with all sorts of errors until I’ve deleted all the views and all the tables from the target database, it then worked, but: Magically all the views from source database appeared to be real tables in the target database! Obviously that didn’t work out and the application that suppose to use the data failed.

Next I’ve tried the generate scripts option, but that only let me replicate the structure of the source database – at least correctly.

Another huge disadvantage of the export tool is that it doesn’t let you choose what data to export at all, in my case ideally triggers and stored procedures should not be migrated with the data, just need the table and the view objects only and their data.

My question is, how am I suppose to export the 40Gb Database in MS SQL 2005 server?

Answer 1

but: Magically all the views from source database appeared to be real tables in the target database!

Of course it creates table, it's a Data! Import/Export and where else should it write the data of a view to then to a table.

Use Data Import/Export wizard only to copy the table incl data to the target database and then generate SQL scripts for all views, stored procedures and also the indexes for the tables, because the Data Wizard don't do that.

Optional you could use a SSIS package to copy all/selected objects of a database to an other db.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Of course it creates table, it's a Data! Import/Export and where else should it write the data of a view to then to a table.

Funny, I would expect it to be smart enough to tell what a view is and only move the structure for that beast?

Anyway worked out how to get around the stupid of the tool. Blown all the tables in the target DB, kept the views, then in the export tool manually un-ticked all the views to export just the data, and it worked well.