An hour ago I needed to use the Data Generation Plan to add generated data to my database. After setting up the generation plan I executed it. I got the following error:

"violation of primary key constraint 'PK_ '.Cannot insert duplicate key in object 'Pk_...'"

Apparently Visual Studio tries to insert the ID's and starts counting with 1. This will work fine on an empty database, but not in this case. I searched a while to find a solution, but couldn't find any. This is what I did to solve the problem (it is a little bit of hacking, but it works):

At first I opened the .dgen file (there is a directory named "Data Generation Plans" in your database solution) with notepad++. The id's look something like this:

<Element Type="ISql100SimpleColumn" Name="[dbo].[Table].[ID]">
    <Property Name="IsNullable" Value="False" />
    <Property Name="IsIdentity" Value="True" />
    <Relationship Name="TypeSpecifier">
        <Entry>
            <Element Type="ISql90TypeSpecifier">
                <Relationship Name="Type">
                    <Entry>
                        <References ExternalSource="BuiltIns" Name="[int]" />
                    </Entry>
                </Relationship>
            </Element>
        </Entry>
    </Relationship>
    <Annotation Type="ColumnConfigAnnotation">
        <Property Name="Selected" Value="True" />
        <Property Name="Seed" Value="5" />
    </Annotation>
</Element>

My first try was to set the IsIdentity property to false. Visual studio still handles the field as a readonly field. So this isn't a solution.

The second try was to remove the IsIdentity property. After saving Visual Studio asked me to update the plan. After the update Visual Studio asked me to update the plan with the latest database properties. Here you will have to decline. If you do this, your changes will be undone.

After this you will be able to set the minimal value for the id-column. Fill in the highest available id from you database +1 and you're almost good to go!

SQL Server will not accept identity insert by default. You must enable this before you run the plan. Another way to do this is to temporarily alter the tables you want to use and disable the Identity Seed. If you run the Generation Plan now, it will run ok.

Of course be very cautious. You are disabling 'safety controls' and you will be able to mess up your database.