0

I have many records and I want to fire a batch insert query in MYSQL and MSSQL using JDBC:

My query is : INSERT INTO dentalitems SET itemID = ?, categoryid = ?, treatmentAreaID = ?, paintTypeID=?, procedurecode=? ON DUPLICATE KEY UPDATE itemID = ?, procedurecode = ?

The query above is working fine for MYSQL - it will insert the record into the table but if there is a duplicate key then it will perform an update.

My problem is that I want to perform the same above operation in SQL Server also but the query above is only for MYSQL. I searched for an SQL Server query but I did not find any.

In my table itemID and procedurecode are unique columns.

How can I do the same thing with MSSQL Server?

Alex
  • 3,029
  • 3
  • 23
  • 46
Mahesh
  • 1,063
  • 1
  • 12
  • 29

1 Answers1

0

Try this. Table Location (LocationId INT PrimaryKey, LocationName VARCHAR)

MERGE dbo.Location AS target
USING (SELECT 100, 'Location Name') AS source (Id, Name)
    ON (target.LocationId = source.Id)
WHEN MATCHED THEN UPDATE SET LocationName = source.Name
WHEN NOT MATCHED THEN INSERT (LocationId, LocationName) VALUES (source.Id, source.Name);

->

MERGE dbo.Location AS target
USING (SELECT AId, AName FROM A) AS source (Id, Name)
    ON (target.LocationId = source.Id)
WHEN MATCHED THEN UPDATE SET LocationName = source.Name
WHEN NOT MATCHED THEN INSERT (LocationId, LocationName) VALUES (source.Id, source.Name);
Mr Phuc 87
  • 184
  • 2
  • 7
  • Sorry, I don't understand you. So, where does data come from for inserting or updating? – Mr Phuc 87 Mar 19 '15 at 11:04
  • I have static data and I want to insert all the record into table but if table has particular record then I want to update it. I don't want to perform any selection in between – Mahesh Mar 19 '15 at 11:07