Don't Trust the Wizard

If you need to move data from one table into a new table, or even tables in a database into another database, the Import/Export Wizard in SQL Server Management Studio looks pretty tempting. Set up a source & destination, click a few buttons, kick back with a cup of tea and watch the progress bars, right?

It turns out that the wizard just isn’t as smart as it may seem. If you’re not careful, you won’t get what you’re expecting. Let’s check it out.

We’ll start by creating a real simple table in a database, containing a primary key and a computed column.

1
2
3
4
5
6

Create table sample.dbo.SourceTable (
   RowId int identity(1,1) not null primary key
   , Num1 int not null
   , Num2 int not null
   , Total as (Num1+Num2)
); 

Let’s populate it with a few rows of data, then update some of that data to make sure the computed column is working. Remember, this is just to demonstrate the idea.

1
2
3
4
5

insert into sample.dbo.SourceTable (Num1, Num2) values (1,2);
go 100
select top 5 * from sample.dbo.SourceTable order by RowId;
update sample.dbo.SourceTable set Num1 = Num1 * RowId where RowId<= 3;
select top 5 * from sample.dbo.SourceTable order by RowId; 

Great! We’ve got data, the computed columns are working, let’s copy it over to a new table in another database. We’ll just going to click Next, Next, Next through the wizard this time around.

Success! Our table has been copied and the data’s all there.

1
2

 
select top 5 * from Sample2.dbo.SourceTable order by RowId; 

Let’s do some work on our new table and check out the results.

1
2
3

select top 5 * from Sample2.dbo.SourceTable order by RowId;
update Sample2.dbo.SourceTable set Num2 = Num2 * RowId where RowId < 3;
select top 5 * from Sample2.dbo.SourceTable order by RowId; 

Woah! That’s not right. That Total column is supposed to be Num1 + Num2, and last time I checked 2 + 4 was not 4. Let’s keep going and try adding a new record the same way it was done earlier.

1

insert into Sample2.dbo.SourceTable (Num1, Num2) values (100,200); 

Huh. Now that’s really odd, isn’t it? RowId is supposed to be an identity - we shouldn’t have to populate it. What is going on here? Let’s script out the table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

 USE [Sample2] GO

/****** Object: Table [dbo].[SourceTable] Script Date: 2015-11-10 22:36:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SourceTable](
  [RowId] [int] NOT NULL
  , [Num1] [int] NOT NULL
  , [Num2] [int] NOT NULL
  , [Total] [int] NULL
  ) ON [PRIMARY]

GO 

This is all kinds of wrong! What happened to the primary key? Or the computed column? Well, it turns out that the wizard isn’t that smart, and if you just take all the default values, you’re going to get burned. Let’s go back to the wizard and click that Edit Mappings button in the Select Source Tables and Views screen.

Well…that looks like what we got above. And it’s not what we wanted. If we click Edit SQL, this is confirmed - the table being created is not defined the same way the source table is being defined.

Fortunately, we can edit the SQL here and make it match the source table definition, then finish the wizard.

OK, data’s copied - what do we have?

1
2
3
4
5

select top 5 * from Sample3.dbo.SourceTable order by RowId;
update Sample3.dbo.SourceTable set Num2 = Num2 * RowId whereRowId < 3;
select top 5 * from Sample3.dbo.SourceTable order by RowId;
insert into Sample3.dbo.SourceTable (Num1, Num2) values(100,200);
select * from sample3.dbo.SourceTable where rowid >= 100 order by RowId; 

Everything’s there, and it’s working the way it’s supposed to. Lesson learned: don’t blindly trust the defaults, especially the ones in a wizard. Double-check everything, and then verify that your schema works the way you expect it to before doing any business with it.