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]

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.

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