CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2sql-updateinsert-update
vision
vision

Reputation: 455

Insert records into a table if it doesnt exists or else add the new record with existing record?

I am selecting multiple rows and inserting them into another table i want to make sure that if already the record exists i want to update the existing row or else just insert New record.

Table:

ID   VALUE   SOFid

3     44     5555
5     43     5555
8     88     8888

My new records are 

INSERT INTO TABLE (6,88,5555)
INSERT INTO TABLE (1,89,5555)
INSERT INTO TABLE (5,99,5555)
INSERT INTO TABLE (8,76,5555)

How can i go forward if i get same ID i need to add value to existing record ..

    Proc for my query:
   CREATE  PROC Test
     @ID INT,
     @VALUE1 INT,
     @@SFOid int
    AS
    BEGIN
    IF @ID=(SELECT ID FROM Table WHERE ID= @ID AND SFOid=@SFOid)
    BEGIN
    UPDATE Rotation 
    SET value=@VALUE1+(SELECT value FROM Rotation WHERE ID=@ID AND SFOid=@SFOid)
    WHERE ID=@ID AND SFOid=@SFOid
    END
    ELSE 
    INSERT INTO Table
    VALUES(@ID,@VALUE1,@SFOid)
    END

Upvotes: 1

Views: 1503

Answers (3)

M.Ali
M.Ali

Reputation: 69524

Try using Merge Statement seems to perform really well in this kind of situation when you doing multiple operations on one table.

MERGE Table1 AS tbl1

USING (SELECT Column1, Column2, Column3, …..FROM Table2) AS tbl2

ON tbl1.Column1 = tbl2.Column1

WHEN MATCHED THEN

UPDATE

SET -- Your update whatever you are updating

WHEN NOT MATCHED THEN

INSERT INTO TableName(Column1, Column2, Column3......)

VALUES(Value1, Value2, Value3,......);

GO

Upvotes: 0

seshan
seshan

Reputation: 99

I am not sure if this is the optimal solution... But if you want to do it the way you want..

Scan the table if the record already exists.. if yes update the specific column or create a new column will all the feilds..

like in java you can do this way...

while(rs.next){
if(id==// your way of defination of id){
update your column using alter table...}
else
{
insert into....
}
}

Sorry for no syntax, but the idea is projected..

Upvotes: 2

SchmitzIT
SchmitzIT

Reputation: 9552

Try using the MERGE statement.

On another note, why are you inserting ID numbers? Those are typically automatically generated by the DB itself.

Upvotes: 3

Related Questions