SQL Server comes equipped with a fantastic wizard for importing/exporting data. It’s so simple : just right click on your database > Tasks > Export Data (or Import Data). What could be simpler, right ?
No, sir, this is one deceptive tool…
Suppose I create a table like this : just an integer column with an identity value, and a varchar column :
create table Birdman (
bid int identity(1,1),
Lets say I want to export this table to another database. I fire up the Export Wizard and run through the steps. In the “Column Mappings” section, I dutifully click the “Enable identity insert” checkbox as I know this will involve an identity column :
And it succeeds.
Now script out the newly created table in the other (target) database, and see what you get :
CREATE TABLE [dbo].[Birdman](
[bid] [int] NOT NULL,
[bname] [varchar](100) NULL
) ON [PRIMARY]
Dude, where’s my identity property ?????
Now as it turns out, the SQL Server Import/Export Wizard has a nasty way of ignoring the Identity setting when it copies a table across. It also conveniently ignores Constraints, Indexes, etc. In short, it is thoroughly unreliable to transfer database objects with this method.
A safer approach would be to script out the object and create it on the target. Once the table is created, use the Import/Export Wizard to copy data across databases.
Hope this helps someone out there !