locked
Queries to insert new records if they are not existed in the table and updated if they are existed. RRS feed

  • Question

  • User-34860367 posted

    Hi all,

    I have two tables: #tempTable (name, phone, city, zipcode) and tblcustomer (id, name, phone, city, zipcode) where id is unique primary key . The #tempTable is stored data which imported from Excel file and tblCustomer need a unique key id for doing CRUD procedure implementation.

    Sample data

    first attempt to do insert from #tempTable to tblCustomer, new records inserted 2 from tempTable

    #tempTable 

    John Smith, (111) 11-1111, Chicago, 34124

    Kim Lee, (222) 22-2222, New York, 43567

    tblCustomer 

    1, John Smith, (111) 11-1111, Chicago, 34124

    2, Kim Lee, (222) 22-2222, New York, 43567

    Secondlt user attempt to do insert from #tempTable to tblCustomer: the data imported to TempTable can be change

    #tempTable 

    John Smith, (111) 11-1111, Chicago, 34124

    Kim Lee, (222) 22-2222, Dallas, 67029

    Jane Dee, (333) 33-3333, Miami, 39012

    Expected tblCustomer records

    John Smith, (111) 11-1111, Chicago, 34124

    Kim Lee, (222) 22-2222, Dallas, 67029

    Jane Dee, (333) 33-3333, Miami, 39012

    In a brief, I want to write stored procedure that perform to check record by record as the following:

    1. first query to insert the records from #tempTable into tblCustomer table if they are not existed based on name , phone.

    2. second query to update the records for other fields (city. zipcode)  if they are existed in the table and have the same name, phone.

    How do I can loop through and compare records based on name, phone for insert and update? 

    I really appreciate your time if any T-SQL expertise can help me  on above sproc.

    Thanks in advance.

    Friday, July 1, 2016 3:50 AM

Answers

  • User-157633207 posted

    Hi avt2k7,

    You can use "Merge Statement". It is capable of inserting if no records are matched in the table, if there is any matching records it will update the records.

    MERGE INTO dbo.tblCustomer tc
    USING
    (
    	SELECT * FROM #tempTable
    ) temp 
    ON tc.Name = temp.Name
       AND tc.Phone = temp.Phone
    WHEN NOT MATCHED 
    THEN INSERT
    (
         Name,
         Phone,
         City,
         ZipCode
    )
    VALUES
    (
         temp.Name,
         temp.Phone,
         temp.City,
         temp.ZipCode
    )
    WHEN MATCHED THEN 
    UPDATE SET 
               City = temp.City,
               ZipCode = temp.ZipCode;	

    Try this I think it will work for you..

    Thanks...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 3, 2016 2:48 PM
  • User-62323503 posted

    Implement merge. 

    Refer below post:

    http://www.itdeveloperzone.com/2013/03/merge-statement-in-sql-server.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 14, 2016 8:33 AM

All replies

  • User-359936451 posted

    First off, I think you have some significant design issues. First, checking whether a record exists on name and phone is tricky, here is why, although most people have personal cell phones, what if you date record in included the same name like John Doe Sr and John Doe Jr. and since the son still lives in his parents house, they have the same phone number.

    the same holds for City, St. There street address might be different but what if they live in the same city and st.

    Those issues aside. You should first query for the keyID of the record for all values. And don't forget to handle the address changing, say the person moved and your database was never updated.

    So,

    currKeyID = "SELECT keyID FROM userTable WHERE f.Name = field1, l.Name = field2, S.Add1- field3, S.Add2- field4, SAdd3 = field4, city= field5, st= field6, zip= field6, phone=field7, email=field8, acctNum = field9"

    This is a little over kill but you get my drift, the extra address fields are for apt # and such some addresses have these. You can reduce this list as necessary. Ideally you would have a list of users and compare as many fields as possible with the listing, as if a customer service rep trying to locate an account record.

    If no match then insert or if found but some fields need to be updated, the record could be opened in .net then edited and updated back to the database.

    UPDATE userTable SET S.Add1 =newField1, city = newfiled2, WHERE keyID = currKeyID

    if currKeyID isNull then

    INSERT INTO userTable (all your fields) VALUES {all you values comma seperated}  look up the syntax here.

    hope this gets you started.

    Friday, July 1, 2016 8:32 PM
  • User-34860367 posted

    Thanks for your inputs. You're right on problem statement. Sorry for this inconvenience. This is a unreal design. Just need to get help on T-SQL query logic  for my demo data only. 

    Friday, July 1, 2016 9:54 PM
  • User-157633207 posted

    Hi avt2k7,

    You can use "Merge Statement". It is capable of inserting if no records are matched in the table, if there is any matching records it will update the records.

    MERGE INTO dbo.tblCustomer tc
    USING
    (
    	SELECT * FROM #tempTable
    ) temp 
    ON tc.Name = temp.Name
       AND tc.Phone = temp.Phone
    WHEN NOT MATCHED 
    THEN INSERT
    (
         Name,
         Phone,
         City,
         ZipCode
    )
    VALUES
    (
         temp.Name,
         temp.Phone,
         temp.City,
         temp.ZipCode
    )
    WHEN MATCHED THEN 
    UPDATE SET 
               City = temp.City,
               ZipCode = temp.ZipCode;	

    Try this I think it will work for you..

    Thanks...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 3, 2016 2:48 PM
  • User-34860367 posted

    Hi balu.devara

    I tried your query but it produced the multi-duplicated records. Another trying on above query. It worked.

    Thanks. 

    Saturday, July 9, 2016 5:15 AM
  • User-1404113929 posted

    hi,

    can you try following code it will work for you..

    MERGE INTO dbo.tblCustomer tc
    USING
    (
    	SELECT * FROM #tempTable
    ) temp 
    ON Convert(varbinary,tc.Name) =Convert(varbinary, temp.Name)
       AND Convert(varbinary, tc.Phone) =Convert(varbinary,temp.Phone)
    WHEN NOT MATCHED 
    THEN INSERT
    (
         Name,
         Phone,
         City,
         ZipCode
    )
    VALUES
    (
         temp.Name,
         temp.Phone,
         temp.City,
         temp.ZipCode
    )
    WHEN MATCHED THEN 
    UPDATE SET 
               City = temp.City,
               ZipCode = temp.ZipCode;

    Wednesday, July 13, 2016 4:38 AM
  • User-62323503 posted

    Implement merge. 

    Refer below post:

    http://www.itdeveloperzone.com/2013/03/merge-statement-in-sql-server.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 14, 2016 8:33 AM