SQLTeam.com | Weblogs | Forums

Insert update from one table to another


#1

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


#2

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

#3

We use SQL Server 2005....


#4

the use the upsert model (posted example)


#5

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.


#6

It depends. More efficient to code Merge though.


#7

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


#8
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

#9

I believe that this code will only Update existing records, I also need to Insert a new one if the record does not exists.


#10

add a where clause to the insert:

Where customercode not in (select customercode from customermaster)

#11

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


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

#13

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

#14

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"


#15

OK -- so add that to your WHERE clause


#16

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


#17

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