I have two tables Table1 with the following fields:
CustomerCode, Address, City, Contact, Email, Branch, SalesPerson, Area
CustomerMaster
CustomerCode, Address, City, Contact, Email, Branch, SalesPerson, Area
I need to update CustomerMaster Table with new contact information if the customer already exists and insert a new record into CustomerMaster if the record does not exist. My PK key is CustomerCode
What is the most efficient way to read, update the records?
Most efficient: MERGE (SQL Server 2012 and up)
Almost as good:
begin trans
select ....
from customermaster with (updlock)
where customercode in (select customercode from table1)
if @@rowcount > 0 begin
update cm
set cm.address = t1.address, ... etc
from CustomerMaster cm
join table1 t1
on cm.customercode = t1.customercode
where cm.address <> t1.address
or cm..... <> ...etc.
end
insert into customermaster (cols)
select (cols) from table1
where customercode not in (select customercode from customermaster)
commit
Merge is not actually that efficient, in fact, it often performs worse than an UPDATE and INSERT. For now, stick to UPDATE and INSERT even through SQL 2012.
This is my help, I am getting error "Incorrect Syntac near ',' line 17 still having problems
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[Update_Corp_GeoLocationTbl]
as
Begin
--UPDATE
IF EXISTS(SELECT * FROM dbo.TESTLOCATOR_TABLE WHERE CUSTOMER_ID = dbo.LOCATOR_CORP_GEOLOCATION.CUSTOMER_ID)
BEGIN
UPDATE dbo.LOCATOR_CORP_GEOLOCATION
SET LATITUDE = dbo.TESTLOCATOR_TABLE.LATITUDE, LONGITUDE = dbo.TESTLOCATOR_TABLE.LONGITUDE
WHERE dbo.TESTLOCATOR_TABLE.CUSTOMER_ID = dbo.LOCATOR_CORP_GEOLOCATION.CUSTOMER
END
ELSE -- Otherwise INSERT new row
INSERT INTO dbo.LOCATOR_CORP_GEOLOCATION
(CUSTOMER,LATITUDE,LONGITUDE)
Select (CUSTOMER_ID ,LATITUDE,LONGITUDE)
FROM CustomerDB.dbo.TESTLOCATOR_TABLE
END
UPDATE L
SET LATITUDE = T.LATITUDE,
LONGITUDE = T.LONGITUDE
FROM dbo.LOCATOR_CORP_GEOLOCATION L
INNER JOIN dbo.TESTLOCATOR_TABLE T ON T.CUSTOMER_ID = L.CUSTOMER
INSERT INTO dbo.LOCATOR_CORP_GEOLOCATION (
CUSTOMER,
LATITUDE,
LONGITUDE
)
SELECT CUSTOMER_ID,
LATITUDE,
LONGITUDE
FROM CustomerDB.dbo.TESTLOCATOR_TABLE
I don't quite understand where exactly I put this code to append / insert a new record if not existedl (Where customercode not in (select customercode from customermaster)
(
UPDATE L
SET L.Latitude = T.LATITUDE,
L.Longitude = T.LONGITUDE
FROM dbo.LOCATOR_CORP_GEOLOCATION L
INNER JOIN dbo.TESTLOCATOR_TABLE T ON T.CUSTOMER_ID = L.Customer
INSERT INTO dbo.LOCATOR_CORP_GEOLOCATION (
Customer,
Latitude,
Longitude
)
SELECT CUSTOMER_ID,
LATITUDE,
LONGITUDE
FROM CustomerDB.dbo.TESTLOCATOR_TABLE
--UPDATE of only existing rows.
UPDATE L
SET L.Latitude = T.LATITUDE,
L.Longitude = T.LONGITUDE
FROM dbo.LOCATOR_CORP_GEOLOCATION L
INNER JOIN dbo.TESTLOCATOR_TABLE T ON T.CUSTOMER_ID = L.Customer
--INSERT of only new rows.
INSERT INTO dbo.LOCATOR_CORP_GEOLOCATION (
Customer,
Latitude,
Longitude
)
SELECT T.CUSTOMER_ID,
T.LATITUDE,
T.LONGITUDE
FROM CustomerDB.dbo.TESTLOCATOR_TABLE T
WHERE NOT EXISTS(SELECT 1 FROM dbo.LOCATOR_CORP_GEOLOCATION L WHERE T.CUSTOMER_ID = L.Customer)
I would add a WHERE clause to the UPDATE to prevent update where there is no substantive change
WHERE L.Latitude <> T.LATITUDE
OR L.Longitude <> T.LONGITUDE
if either could be NULL then you would need a more complex WHERE clause
WHERE (L.Latitude <> T.LATITUDE OR (L.Latitude IS NULL AND T.LATITUDE IS NOT NULL) OR (L.Latitude IS NOT NULL AND T.LATITUDE IS NULL))
OR (L.Longitude <> T.LONGITUDEOR (L.Longitude IS NULL AND T.LONGITUDEIS NOT NULL) OR (L.Longitude IS NOT NULL AND T.LONGITUDEIS NULL))
I have added where clause to my Update part but the Insert part won't insert anything.
UPDATE L
SET L.Latitude = T.LATITUDE,
L.Longitude = T.LONGITUDE
FROM dbo.LOCATOR_CORP_GEOLOCATION L
INNER JOIN dbo.TESTLOCATOR_TABLE T ON T.CUSTOMER_ID = L.Customer and
T.TYPE = L.Distributor_id
where L.Distributor_id = 'Corp'
--INSERT of only new rows.
INSERT INTO dbo.LOCATOR_CORP_GEOLOCATION (
Customer,
Distributor_id,
Latitude,
Longitude
)
SELECT T.CUSTOMER_ID,
T.TYPE,
T.LATITUDE,
T.LONGITUDE
FROM CustomerDB.dbo.TESTLOCATOR_TABLE T
WHERE NOT EXISTS(SELECT 1 FROM dbo.LOCATOR_CORP_GEOLOCATION L WHERE T.CUSTOMER_ID = L.Customer AND
T.TYPE = L.Distributor_id) AND
T.TYPE = 'Corp'