CODEWITHSUNDEEP
sqldatabasevb.netwinformsvisual-studio-2010
Itz.Irshad
Itz.Irshad

Reputation: 1024

Primay Key conflicts on insertion of new records

In a database application, I want to insert, update and delete records in a table of database. Table is as below: enter image description here

In this table, Ga1_ID is Primary Key. Suppose, I insert 5 records as show currently. In second attempt, if I want to insert 5 other records and if any of these new records contains a primary key attribute which is already present in table it show error. Its fine.

But, when I insert new 5 records... how I can verify these new records's primary key value is not present. I mean, how to match or calculate the already present primary key attributes and then insert new records.

What is the best approach to manage this sort of situation ?

Upvotes: 0

Views: 477

Answers (5)

janos
janos

Reputation: 124646

Do not check existing records in advance, i.e. do not SELECT and then INSERT. A better (and pretty common) approach is to try to INSERT and handle exceptions, in particular, catch a primary key violation if any and handle it.

  1. Do the insert in a try/catch block, with different handling in case of a primary key violation exception and other sql exception types.

  2. If there was no exception, then job's done, record was inserted.

  3. If you caught a primary key violation exception, then handle it appropriately (your post does not specify what you want to do in this case, and it's completely up to you)

If you want to perform 5 inserts at once and want to make sure they all succeed or else roll back if any of them failed, then do the inserts within a transaction.

Upvotes: 1

plalx
plalx

Reputation: 43718

Normally, you would like to have a surrogate key wich is generally an identity column that will automatically increment when you are inserting rows so that you don't have to care about knowing which id already exists.

However, if you have to manually insert the id there's a few alternatives for that and knowing wich SQL database you are using would help, but in most SQL implementations, you should be able to do something like:

IF NOT EXISTS

IF NOT EXISTS(
        SELECT 1
        FROM your_table
        WHERE Ga1_ID = 1
    )
        INSERT INTO ...

SELECT WHERE NOT EXISTS

INSERT INTO your_table (col_1, col_2)
SELECT col_1, col_2
FROM (
    SELECT 1 AS col_1, 2 AS col_2
    UNION ALL
    SELECT 3, 4
) q
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table
    WHERE col_1 = q.col_1
)

For MS SQL Server, you can also look at the MERGE statement and for MySQL, you can use the INSERT IGNORE statement.

Upvotes: 0

Superman
Superman

Reputation: 3083

If you're using SQL Server 2012, use a sequence object - CREATE SEQUENCE.
This way you can get the next value using NEXT VALUE FOR.

With an older SQL Server version, you need to create the primary key field as an IDENTITY field and use the SCOPE_IDENTITY function to get the last identity value and then increment it manually.

Upvotes: 0

Freelancer
Freelancer

Reputation: 9074

use following query in dataadapter:

   da=new SqlDataAdapter("select  Ga1_ID from table where  Ga1_ID=@pkVal",conn);
    DataSet=new DataSet();
    da.fill(ds);

//pass parameter for @pkVal

    da.SelectCommand.Parameters(1).Value = pkValue;    

    if(ds.Tables[0].Rows.Count>0) //If number of rows >0 then record exists
     BEGIN
     messagebox.show("Primary key present");
     END

Hope its helpful.

Upvotes: 1

Connr
Connr

Reputation: 408

you can do a lookup first before inserting. 

IF EXISTS (SELECT * FROM tableName WHERE GA1_id=@newId)
    BEGIN
        UPDATE tableName SET Ga1_docid = @newdocID, GA1_fieldNAme = @newName, Ga1_fieldValue = @newVal where  GA1_id=@newId
    END
ELSE
    BEGIN
        INSERT INTO tableName(GA1_ID, Ga1_docid, GA1_fieldNAme Ga1_fieldValue) VALUES (value1,val2,value3,value4)
    END

Upvotes: 0

Related Questions