How to insert data into table after validation

[pavelcc]

I need to insert data into a table after validating that the data doesn't already exist on the table.

In addition to that I need to update data if there are any changes.

I got the insert part as:

DECLARE 
@SUGAR_ID VARCHAR (150)    
SELECT @SUGAR_ID =  ID FROM DEVSUGARCRM.DBO.ACCOUNTS --@SUGAR_ID 
    IF NOT EXISTS (SELECT * FROM ADVERTISER WHERE SUGAR_ID = @SUGAR_ID)  --NOT EXISTS 
      BEGIN
        INSERT INTO DBO.ADVERTISER
            SELECT      
                A.NAME, 
                B.NAME AS PARENT, 
                AM.USER_NAME AS AM, 
                DIVISION_C, A.BILLING_ADDRESS_COUNTRY, 
                AE.USER_NAME AS AE, A.ID AS SUGAR_ID, OOID_C
            FROM  DEVSUGARCRM.DBO.ACCOUNTS A 
                  LEFT OUTER JOIN DEVSUGARCRM.DBO.ACCOUNTS B ON A.PARENT_ID = B.ID
                  JOIN DEVSUGARCRM.DBO.ACCOUNTS_CSTM C ON A.ID = C.ID_C
                  LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AM ON USER_ID6_C = AM.ID
                  LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AE ON A.ASSIGNED_USER_ID = AE.ID
            WHERE A.DELETED = 0
            AND   B.DELETED = 0
 
      END
 
 
--PRINT @SUGAR_ID
/*
 
SELECT * FROM DBO.ADVERTISER
 
DELETE ADVERTISER WHERE SUGAR_ID = '12273c93-8120-791b-a624-4baa1f681215'
 
*/

I still need the update part.
What I need is, to verify each rows/columns to find out if there is any changes. If changed then I will need to update them.

[ck9663]

If you're looking at a small number of records, just update the entire table. It might take it longer to compare each column if there are changes than a direct update.

Here's the update syntax.

Happy Coding!!!

~~ CK

[pavelcc]

@pavelcc
I found the solved it:

DECLARE 
    @NAME VARCHAR(150), 
    @PNAME VARCHAR(150), 
    @AM VARCHAR(150), 
    @DIVISION VARCHAR(150), 
    @B_ADDRESS VARCHAR(150), 
    @AE VARCHAR(150), 
    @SUGAR_ID VARCHAR(150), 
    @OOID_C VARCHAR(150)
 
SELECT @SUGAR_ID =  ID FROM DEVSUGARCRM.DBO.ACCOUNTS                -- GETTING ID AN @SUGAR_ID 
IF EXISTS (SELECT * FROM ADVERTISER WHERE @SUGAR_ID = SUGAR_ID)  -- EXISTS VARIFICATION
 
--***************************Update****************************************************************
BEGIN
SELECT      
    @NAME        =    A.NAME, 
    @PNAME        =    B.NAME, 
    @AM            =    AM.USER_NAME, 
    @DIVISION    =    DIVISION_C, 
    @B_ADDRESS    =    A.BILLING_ADDRESS_COUNTRY, 
    @AE            =    AE.USER_NAME, 
    @SUGAR_ID    =    A.ID, 
    @OOID_C        =    OOID_C
            FROM  DEVSUGARCRM.DBO.ACCOUNTS A 
                  LEFT OUTER JOIN DEVSUGARCRM.DBO.ACCOUNTS B ON A.PARENT_ID = B.ID
                  JOIN DEVSUGARCRM.DBO.ACCOUNTS_CSTM C ON A.ID = C.ID_C
                  LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AM ON USER_ID6_C = AM.ID
                  LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AE ON A.ASSIGNED_USER_ID = AE.ID
            WHERE A.DELETED = 0
            AND   B.DELETED = 0
 
        UPDATE    ADVERTISER SET 
                    OOID = @OOID_C ,
                    Company_Name = @NAME, 
                    Parent_Company = @PNAME, 
                    Account_Manager = @AE,
                    Country_Code = @B_ADDRESS, 
                    Division = @DIVISION, 
                    Account_Executive= @AE 
 
        WHERE Sugar_ID = @SUGAR_ID
END
--*************************Update Ends here**************************************************
--*************************Added to Insert if not updates***************************************
ELSE 
SELECT @SUGAR_ID =  ID FROM DEVSUGARCRM.DBO.ACCOUNTS                        
    IF NOT EXISTS (SELECT * FROM ADVERTISER WHERE SUGAR_ID = @SUGAR_ID)        
      BEGIN
        INSERT INTO DBO.ADVERTISER
            SELECT      
                OOID_C,
                A.NAME,                            -- Company Name
                B.NAME,                            -- AS PARENT 
                AM.USER_NAME,                    -- AS AM 
                A.BILLING_ADDRESS_COUNTRY,        -- country code
                DIVISION_C,                        -- Division                 
                AE.USER_NAME,                    --AS AE, 
                A.ID                            --AS SUGAR_ID 
 
            FROM  DEVSUGARCRM.DBO.ACCOUNTS A 
                  LEFT OUTER JOIN DEVSUGARCRM.DBO.ACCOUNTS B ON A.PARENT_ID = B.ID
                  JOIN DEVSUGARCRM.DBO.ACCOUNTS_CSTM C ON A.ID = C.ID_C
                  LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AM ON USER_ID6_C = AM.ID
                  LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AE ON A.ASSIGNED_USER_ID = AE.ID
            WHERE A.DELETED = 0
            AND   B.DELETED = 0
 
      END
--*****************Ends Here**********************