Importing Identity column data into SQL Server

Updated 23/1/10:
I am in the process of porting some (DNN module) tables from one Microsoft SQL Server database to another. For various reasons, I want the primary key identity column values to remain the same. This is on a shared SQL server, so we don't have admin access, eg to the command line and file system.

The SQL Server Import and Export wizard transfers the data nicely and will preserve the identity values if you do it correctly.

There are two crucial tricks:
- set up the destination table(s) with the correct primary keys etc before the copy
- in the wizard, click Edit Mapping and tick Enable Identity Insert.

----------------------
This is alternative technique which should no longer be needed:

I first export the original data to a temporary database. This creates the tables but but does not create primary keys, set default values etc. However, the copy process does preserve the original primary key identity column values.

By installing the DNN module on the destination system, the tables are created correctly there. However, a wizard import into these tables (even with "Enable identity insert" set) does not preserve the key values.

The trick I found is to upload the data into a new table eg "Form2", and then use the following code to copy the data into the correct "Form" table. The trick is to use the "set identity_insert" statement.

set identity_insert DNN_Form on;

insert into DNN_Form ([FormID],[FormName])
select * from DNN_Form2;

set identity_insert DNN_Form off;

You must list all the required columns in the insert statement.
Only one table can have identity_insert on at a time.

Finally, delete the "Form2" table.

You can check the current insert identity value before and after as follows:

DBCC CHECKIDENT(DNN_Form);