By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,784 Members | 1,188 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,784 IT Pros & Developers. It's quick & easy.

How to insert data into table after validation

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. DECLARE 
  2. @SUGAR_ID VARCHAR (150)    
  3. SELECT @SUGAR_ID =  ID FROM DEVSUGARCRM.DBO.ACCOUNTS --@SUGAR_ID 
  4.     IF NOT EXISTS (SELECT * FROM ADVERTISER WHERE SUGAR_ID = @SUGAR_ID)  --NOT EXISTS 
  5.       BEGIN
  6.         INSERT INTO DBO.ADVERTISER
  7.             SELECT      
  8.                 A.NAME, 
  9.                 B.NAME AS PARENT, 
  10.                 AM.USER_NAME AS AM, 
  11.                 DIVISION_C, A.BILLING_ADDRESS_COUNTRY, 
  12.                 AE.USER_NAME AS AE, A.ID AS SUGAR_ID, OOID_C
  13.             FROM  DEVSUGARCRM.DBO.ACCOUNTS A 
  14.                   LEFT OUTER JOIN DEVSUGARCRM.DBO.ACCOUNTS B ON A.PARENT_ID = B.ID
  15.                   JOIN DEVSUGARCRM.DBO.ACCOUNTS_CSTM C ON A.ID = C.ID_C
  16.                   LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AM ON USER_ID6_C = AM.ID
  17.                   LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AE ON A.ASSIGNED_USER_ID = AE.ID
  18.             WHERE A.DELETED = 0
  19.             AND   B.DELETED = 0
  20.  
  21.       END
  22.  
  23.  
  24. --PRINT @SUGAR_ID
  25. /*
  26.  
  27. SELECT * FROM DBO.ADVERTISER
  28.  
  29. DELETE ADVERTISER WHERE SUGAR_ID = '12273c93-8120-791b-a624-4baa1f681215'
  30.  
  31. */
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.
Jun 2 '10 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Jun 3 '10 #2

P: 2
@pavelcc
I found the solved it:
Expand|Select|Wrap|Line Numbers
  1. DECLARE 
  2.     @NAME VARCHAR(150), 
  3.     @PNAME VARCHAR(150), 
  4.     @AM VARCHAR(150), 
  5.     @DIVISION VARCHAR(150), 
  6.     @B_ADDRESS VARCHAR(150), 
  7.     @AE VARCHAR(150), 
  8.     @SUGAR_ID VARCHAR(150), 
  9.     @OOID_C VARCHAR(150)
  10.  
  11. SELECT @SUGAR_ID =  ID FROM DEVSUGARCRM.DBO.ACCOUNTS                -- GETTING ID AN @SUGAR_ID 
  12. IF EXISTS (SELECT * FROM ADVERTISER WHERE @SUGAR_ID = SUGAR_ID)  -- EXISTS VARIFICATION
  13.  
  14. --***************************Update****************************************************************
  15. BEGIN
  16. SELECT      
  17.     @NAME        =    A.NAME, 
  18.     @PNAME        =    B.NAME, 
  19.     @AM            =    AM.USER_NAME, 
  20.     @DIVISION    =    DIVISION_C, 
  21.     @B_ADDRESS    =    A.BILLING_ADDRESS_COUNTRY, 
  22.     @AE            =    AE.USER_NAME, 
  23.     @SUGAR_ID    =    A.ID, 
  24.     @OOID_C        =    OOID_C
  25.             FROM  DEVSUGARCRM.DBO.ACCOUNTS A 
  26.                   LEFT OUTER JOIN DEVSUGARCRM.DBO.ACCOUNTS B ON A.PARENT_ID = B.ID
  27.                   JOIN DEVSUGARCRM.DBO.ACCOUNTS_CSTM C ON A.ID = C.ID_C
  28.                   LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AM ON USER_ID6_C = AM.ID
  29.                   LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AE ON A.ASSIGNED_USER_ID = AE.ID
  30.             WHERE A.DELETED = 0
  31.             AND   B.DELETED = 0
  32.  
  33.         UPDATE    ADVERTISER SET 
  34.                     OOID = @OOID_C ,
  35.                     Company_Name = @NAME, 
  36.                     Parent_Company = @PNAME, 
  37.                     Account_Manager = @AE,
  38.                     Country_Code = @B_ADDRESS, 
  39.                     Division = @DIVISION, 
  40.                     Account_Executive= @AE 
  41.  
  42.         WHERE Sugar_ID = @SUGAR_ID
  43. END
  44. --*************************Update Ends here**************************************************
  45. --*************************Added to Insert if not updates***************************************
  46. ELSE 
  47. SELECT @SUGAR_ID =  ID FROM DEVSUGARCRM.DBO.ACCOUNTS                        
  48.     IF NOT EXISTS (SELECT * FROM ADVERTISER WHERE SUGAR_ID = @SUGAR_ID)        
  49.       BEGIN
  50.         INSERT INTO DBO.ADVERTISER
  51.             SELECT      
  52.                 OOID_C,
  53.                 A.NAME,                            -- Company Name
  54.                 B.NAME,                            -- AS PARENT 
  55.                 AM.USER_NAME,                    -- AS AM 
  56.                 A.BILLING_ADDRESS_COUNTRY,        -- country code
  57.                 DIVISION_C,                        -- Division                 
  58.                 AE.USER_NAME,                    --AS AE, 
  59.                 A.ID                            --AS SUGAR_ID 
  60.  
  61.             FROM  DEVSUGARCRM.DBO.ACCOUNTS A 
  62.                   LEFT OUTER JOIN DEVSUGARCRM.DBO.ACCOUNTS B ON A.PARENT_ID = B.ID
  63.                   JOIN DEVSUGARCRM.DBO.ACCOUNTS_CSTM C ON A.ID = C.ID_C
  64.                   LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AM ON USER_ID6_C = AM.ID
  65.                   LEFT OUTER JOIN DEVSUGARCRM.DBO.USERS AE ON A.ASSIGNED_USER_ID = AE.ID
  66.             WHERE A.DELETED = 0
  67.             AND   B.DELETED = 0
  68.  
  69.       END
  70. --*****************Ends Here**********************
Jun 3 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.