SQLTeam.com | Weblogs | Forums

Insert update from one table to another

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?

Thanks

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

We use SQL Server 2005....

the use the upsert model (posted example)

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.

It depends. More efficient to code Merge though.

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 believe that this code will only Update existing records, I also need to Insert a new one if the record does not exists.

add a where clause to the insert:

Where customercode not in (select customercode from customermaster)

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

END

--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))

Thanks this works fine the only thing is that I would only require to select records from dbo.testlocator_table with t.type like 'Corp"

OK -- so add that to your WHERE clause

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'

END

run the select part of the insert on its own. What does it return?