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);

Wandering drive letters in Windows

My main computer's motherboard died - long live the new computer! Thankfully, all the data on the two existing drives were OK. A hastily bought 3.5 inch USB drive caddy worked a treat, keeping me going on the laptop.

The new computer was pre-installed with Windows 7 Home Premium on drive C with a separate partition on drive D for data. I have an MSDN subscription and I wanted to set up various versions of Windows multi-booting on the same computer. I have two different systems that I want to run normally, one with Visual Studio 2008 and the other with various older bits of software. I also want to have some different versions of Windows for software testing, ideally Windows 7, Vista and XP, both in x86 and x64 versions.

OK - I know I can run a virtual PC, but I want to use two of these systems regularly at top speed. I haven't tried virtual PC yet, so I decided to stick with what I knew, which is the HyperOs multibooter, though I had to upgrade to the latest version.

The first task was to repartition the disk, to give more partitions, each with a Windows installation, as well as big photo/video partition. I found that there were already two extra partitions, a (Packard Bell?) Recovery Partition and a small System Reserved partition set up by Windows 7, ie 4 Primary partitions in total. The W7 Disk Management tool wanted to convert my disk to Dynamic Disks to give me more partitions, but these cannot be used for booting, so a swift exit was called for.

HyperOS mentioned the Acronis partitioner but this wasn't playing - not sure if it was Windows 7 or the size of the disk. I did have a copy of gparted on CD, but this hadn't worked for me before. Eventually I found Partition Wizard www.partitionwizard.com which has worked brilliantly. I got a free commercial licence for this.

Using Partition Wizard, I deleted (empty) drive D and made quite a few other Logical partitions. Partition Wizard is clever enough to know that it cannot do changes in some circumstances, and so does it on reboot. Partition Wizard can also resize partitions, moving data if need be. So far that's worked fine. (Check your computer power settings don't shut your disk down at an awkward moment.)

Having done that, I could now copy all my precious data onto the new hard disk from the drive caddy.

Anyway, I've now done various Windows installations, some by installing from DVD from Windows, but mostly by installing from DVD at reboot, ie choosing DVD at boot up from the BIOS boot menu - and choosing Custom Setup to choose the install partition. The problem that I have found is that the drive letters that Windows uses and sees seems to change a lot. The original W7 system is on drive C on the "first" partition. I have another W7 on the fifth partition which thinks of itself as being on drive M - fine. However, most other W7 and Vista installations think of themselves as being drive C, even though they are on partition 4, 7 or 8. When I have rebooted in one of these systems, the drive letters are assigned in a fairly random way. The DVD drive is usually drive E but not always.

In Windows Disk Management you change the drive letters - for some drives at least. But there's limited scope for what you can change to. Partition Wizard can do this, and is probably more successful. However there appears to be no way of persuading a Windows on partition 4 (that thinks it is at drive C) to think of itself as being drive L for example.

All these wandering drive letters might not be problem. However my software development stuff and business data has always been carefully set up (for various reasons) to be stored on both drives C and D. I don't tend to put data in "My Documents", "Documents", "Pictures", etc because these are (usually) stored in different locations for each version of Windows. Anyway, I have persuaded partition 2 to be drive D in all the installations so far. However it was a problem that partition 1 kept wandering all over the shop. My solution was to move all my crucial data from drive C onto a bigger drive D. Ok - fairly simple in itself, but I'm still having to work out what dependencies there are in all my scripts.

Another complication in this process was that Windows XP was dying during installation (with a BSOD). This turned out to be because the SATA drives were being accessed using AHCI. Changing the BIOS to use the SATA setting "Native IDE" got the XP installation to work. However I did not want to leave this setting as is, so I change it whenever I want to switch to XP. XP also doesn't recognise many of the motherboard peripherals, eg Ethernet, so the installation is not very useful. The option to press F6 during installation would let me install a suitable driver, but (a) I don't have the driver and (b) the system doesn't have a floppy; it does look as though new motherboard has a floppy interface, but there's no connector soldered in there!

I was also able to add an IDE/PATA cable and drive to the system to connect my old drives, but the installation still did not work if I was in AHCI SATA mode.

I've still many applications to configure and systems to set up, but I'm getting there.