C# How to insert multiple rows in database with primary key

Once a month I get an xml file with customers billings for the last month. I need to store this information in a database. I have a table named transactions. My primary key is the customers_nr. Currently when I'm inserting data, I'm getting error

violoation to primary key......

I'm using a table adapter for inserting. Basically I only need to keep filling the database with information. So is there any command for handling this? Or how should I do

Example of informaton

customer_nr: 12345
Billing_name: Microsoft
Billing_city: Seattle
Billing_amount 300
Billing_name: Mcdonalds
Billing_city: Seattle
Billing_amount 25

customer_nr: 4321
Billing_name: Ikea
Billing_city: N开发者_运维问答ew York
Billing_amount 1200

The error means that you have duplicate value for your primary key in at least two records .

remember that primary key enforces uniqueness for data inside it.

so you can not insert two records in a table with same value for primary key. you can change your primary key to a new field (for example an auto number field) so you removed uniqueness constraint from your old primary key field and then you can insert duplicates in it!

billing datetime i more suitable for the primary key in this relation, alt. customer_nr + billing_datetime.

If you don't have any date and time for billing, or just know the month, you could use customer_nr, year, month as a composite primary key.

Using the customer number is then obviously not a unique value, meaning it cannot be used as a Primary Key.

Create an IDENTITY column, or utilize a GUID with newsequentialid() (pref. as the field default) to achieve a unique key in the table.

Example:

You have the database structure as follows:

• TransactionID (This is the new field, uniqueidentifer with newsequentialid() as the default value)
• Customer_Nr
• Billing_name
• Billing_city
• Billing_amount
• Billing_name
• Billing_city
• Billing_amount

You would then go as follows:

INSERT INTO transactions (Customer_Nr, Billing_name, Billing_city, Billing_amount)
VALUES ('12345', 'Microsoft', 'Seattle', 300);

As you see, you won't need to change any code with values due to the automatically generated (unique) primary key value.

A note regarding newsequentialid() is that if your data requires security / privacy, do not use newsequentialid(), as it is trivial to guess sequences.