Microsoft KB Archive/247825

Knowledge Base

PRB: DTS Copy Table Uses Table Schema of Existing Table

Article ID: 247825

Article Last Modified on 10/30/2003

---

APPLIES TO

• Microsoft SQL Server 7.0 Standard Edition
• Microsoft SQL Server 2000 Standard Edition

---

This article was previously published under Q247825

SYMPTOMS

If you use the Data Transformation Services (DTS) Wizard with the Copy Table option and with the Drop and Re-create Destination Table option, the table that is created uses the schema of the table that was dropped and not the schema of the table to be copied.

CAUSE

This behavior is normal and by design. The wizard correctly creates the destination table as indicated, which defaults to the existing table's schema. Unlike object transfer, the wizard does not overwrite the destination table with the source table schema. You can edit the columns in the destination grid and change the destination schema default or change the SQL directly for the destination schema.

WORKAROUND

Drop the destination table first or use Transfer Object.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create two databases, DB1 and DB2, each containing Table1 with different schemas.
   
   You can use the following TSQL code to create the table schemas:

   /* Drop Object:  Table[DB1].[dbo].[TABLE1]*/ 
        Use DB1
        go
        if exists (select * from sysobjects
           where id = object_id(N'[dbo].[TABLE1]')
           and OBJECTPROPERTY(id, N'IsUserTable') = 1)
           drop table [dbo].[TABLE1]
        GO
        /* Create Object:  Table[DB1].[dbo].[TABLE1] */ 
        CREATE TABLE [dbo].[TABLE1]([First Name][char](10),
            [Last Name][char](10))
        GO
        /*  Can Insert Data into Test.dbo.table1 */ 
        insert dbo.TABLE1 values ('Joe', 'Smith')
        go
        /* Drop Object: Table[DB2].[dbo].[TABLE1]*/ 
        Use DB2
        go
        if exists (select * from sysobjects
           where id = object_id(N'[dbo].[TABLE1]')
           and OBJECTPROPERTY(id, N'IsUserTable') = 1)
           drop table [dbo].[TABLE1]
        GO
        /* Create Object: Table[DB2].[dbo].[TABLE1]*/ 
        CREATE TABLE [dbo].[TABLE1]([FavoriteDay][char](10),
       [FavoriteAnimal][char](10),[FavoriteNumber][int])
        GO
        /*  Can Insert Data into Test1.dbo.table1 */ 
        insert dbo.TABLE1 values ('Sunday','Dog',13)
        go
                           

   NOTE: The test data is not necessary but it helps to better illustrate the point of this problem.

2. Start the Data Transformation Services (DTS) Wizard. Select DB1 as the source database, select DB2 as the destination database, and then connect to the databases.
3. Select the Copy table option. Select Table1, and then click Transform.
4. Select the Create destination table and the Drop and re-create destination table check boxes.
5. Run DTS.
   
   The DTS package should complete successfully, drop the table, and enter data from DB1.dbo.table1 into the wrong schema from DB2.dbo.table1.

Special Considerations

You might not discover this problem until you attempt to insert new data into the new table. This is because you may not notice the change in the table schema at first on very large tables, which have similar table schemas of the table being replaced.

Additional query words: Microsoft Data Transformation Services

Keywords: kbcodesnippet kbprb KB247825

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.