DTS Import/Export... Don't understand

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Import/Export... Don't understand

Author

Topic

smoi
Starting Member

8 Posts

Posted - 2007-05-15 : 03:25:20

I try to use MS SQL Server 2000 SP4, the DTS Import/Export Wizard in EM to export the data. I right hand click on the database I want to export, (because in the database has lots of tables, so I export by the database), and export it to an Excel file.

Then I use DTS Import/Export Wizard to import the data from the excel file into same database, when what I got is the following when I check using Query Analyzer:

Under User Tables for the same database, I got

dbo.CustomerRefTable
DBA.CustomerRefTable
dbo.CustomerRefTable$

Before I did the Import and Export I only have DBA.CustomerRefTable... Why are there more tables appearing after the Import and Export? This happens also for all the tables...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-15 : 03:29:30

While importing an excel file, you should specify the destination table if it exists. Otherwise, DTS will try to create a table with name matching that of Sheet name of excel file you are importing.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

smoi
Starting Member

8 Posts

Posted - 2007-05-15 : 03:57:42

Thanks so much Harsh Athalye for your prompt response... There's one more question I would like to ask...

So when I didn't specify the destination tables during import of the database from excel file, does that mean my existing table's data won't be overwritten? Because I want to overwrite the existing table's data...

smoi
Starting Member

8 Posts

Posted - 2007-05-15 : 03:58:41

please correct me if I'm wrong... I just want a clarification... Thank you so much

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-15 : 04:07:12

In Import/Export wizard, when you specify destination table, there is an option in the transformation dialog box where you can specify whether you want to:

1. Drop and recreate destination table
2. Delete existing records in dest table
3. Append rows in the existing table

Choose the desired option as per your requirement.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

smoi
Starting Member

8 Posts

Posted - 2007-05-15 : 04:32:44

Errmmm OK, I now specify the Destination table... (I try with one table only this time) It did not create many duplicates tables already... But somehow it replace the table with blank values. During exporting I can see the Status complete with values, but during importing I see the status completed with (0), why is that?

I tried with all options
2. delete rows in destination table
3. append rows in existing table
It gave me blank values after import.

I check with my excel file, there are values, not blank.

If I try with option to drop and recreate tables, it recreate the table but with Read-Only......

smoi
Starting Member

8 Posts

Posted - 2007-05-15 : 04:35:37

Oh ya, just to let u know... in my server I don't have Excel... I use my other pc to view the excel file after i export, is it because it doesn't work if my server don't have excel?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources