SQL 2005 duplicating rows in Export even when told to not. 1

[ironhide1975]

I'm trying to export data from one table to another. I use the SQL export command and copy tables, telling it to drop rows, enable idenity insert and drop and recreate the tables. When I do this, I get a successful completion but the rows are then duplicate (appended) in the table, not replaced.
This is a bug, or am I doing something wrong. The work around of course is to drop the tables manually, recreate them with a script and then copy the data, but I would like to advoid having to recreate the tables each time I want to copy data over. Please help.

 

[AlexCuse]

I am not so clear on what you are trying to do with this one. How would there be data in a table taht was already dropped?

I think your answer is going to be using a T-SQL query as your data source for adding new rows, followed by an update query to modify existing rows that have changed. Without more informatino though it is hard to say.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ironhide1975]

Simply want to drop all records in destination table and insert new records. A basic copy from one database to another.

 

[AlexCuse]

If you are deleting all the records in the destination table, then the duplicates must exist in your source table.

Ignorance of certain subjects is a great part of wisdom

 

[ironhide1975]

This is not the case. When I conduct a SQL 2005 copy using the Export Wizard, it copies over the data, and appends instead of replace the data. Thus I get the same extact records twice, three, or more times in the database. It doesn't want to drop and rebuild the tables, even though I specify this in the query.

 

[AlexCuse]

why don't you post your code? I am sure it is something simple being 'lost in translatin' as it were.

Ignorance of certain subjects is a great part of wisdom

 

[ironhide1975]

I'm not using any code, I'm using the SQL 2005 management console export feature.

Here's the steps I take

1) SQL Export Wizard
2) Choose source and destination database
3) Select rows
4) Select Optimize Many tables, run as a transaction
5) De-select views, only select tables
6) Edit mappings to delete rows in destinationt able, drop and re-create destination table, and enable identity insert.
7) Run the transaction.

If completed successfully I get something like this

ID Content
1 A
2 B
3 C
1 A
2 B
3 C

 

[AlexCuse]

I think if you are trying to update existing records, and append new records, that you will need to get out of the wizard. I have only used it once before and found it very limiting.

You could probably add a step through the wizard to delete all records from your destination table, and then insert all new records.

What is the source database?

Ignorance of certain subjects is a great part of wisdom

 

[ironhide1975]

Is there an easy way to do this? This was so easy to do in 2000.
Both databases are SQL 2005.

 

[AlexCuse]

If you are using SQL 2005, you should check out this forum:

forum1555

SSIS is the 2005 equivalent of DTS. I have started messing around with these, but I am in no way qualified to give advice on them.

Another note, you might be better off doing this with a T-SQL query if this is just a straight copy.

Good LUck,

Alex

Ignorance of certain subjects is a great part of wisdom