SQL Query to update or insert row with If Exists statement not working

Question

previously while posting i missed out table b which hold urn and org_ref . I have three tables. Table a ,b and c, where I have to update the column "total Emp" of table c based on the URN Key in table a. Table B doesnt have URN column but it has Org_ref which so to connect the table a and table c we need to join table b on a.urn=b.urn. I have written a query to loop through the record and do the insert or update, but it's not working and doesn't update. Just insert with blank "Total employee" in table b.

table a

   |urn  |total employee
   |333  |25
   |123  |26
   |21   |2
   |11   |23

Table B

   |urn  |org_ref
   |333  |1234
   |123  |343
   |21   |2
   |11   |23

Table C

   org_ref    total employe 
   1234            0       --should update from table a 
    343            0       --should update from table a 
                           --add record from table a org_ref 21
                           --add record from table a org_ref 11

Here is my script

Create table #orgs(
iorgrowid int identity (1,1),
ORG_REF  varchar(255),
Total_Leader int,
urn int
)


insert into #orgs

select  ORG.organisation_ref,s.[TOTAL_LEADERSHIP_ROLE],s.urn

    from DiTestDatabase.dbo.organisation org

    JOIN DataConversion.dbo.School_plus_download_NI s on org.total_headcount=s.urn 

DECLARE 
@iReturnCode int,
@iNextcodeRowId int,
@icodeRowId int,
@iCurrentcodeRowId int,
@iNextattRowId int,
@iCurrentattRowId int,
@icodeLoopControl int,
@ORGREF INT,
@counter int,
@Ly_employees int,
@orgnamecount int,
@add1count int,
@urn int


SELECT @iCodeLoopControl = 1
SELECT @iNextCodeRowId = MIN(iorgrowId)
FROM #orgs


IF ISNULL(@iNextCodeRowId,0) = 0
BEGIN
SELECT 'No data in found in table!'
RETURN
END


SELECT
@iCurrentcodeRowId = iorgrowId,
@ORGREF=ORG_REF,
@Ly_employees=Total_Leader,
@urn=urn
FROM #orgs
WHERE iorgRowId = @iNextcodeRowId


set @counter = 0    
set @orgnamecount=0
set @add1count=0

--Start the main processing loop.
WHILE @iCodeLoopControl = 1



BEGIN


        begin
          set @counter = @counter + 1
         print 'The counter is ' + cast(@counter as char)  + cast(@ORGREF as char)
        end

IF EXISTS (SELECT OG.ORGANISATION_REF
FROM ORG_ACCOUNT OG WHERE OG.ORGANISATION_REF= @ORGREF
)

BEGIN
  UPDATE ORG_ACCOUNT
  SET LY_EMPLOYEES= (SELECT SP.TOTAL_LEADERSHIP_ROLE
            FROM DataConversion.dbo.School_plus_download_NI SP
            JOIN ORGANISATION ORG ON ORG.TOTAL_HEADCOUNT=SP.URN
            JOIN ORG_ACCOUNT OA ON OA.ORGANISATION_REF=ORG.ORGANISATION_REF
            WHERE OA.ORGANISATION_REF=@ORGREF)
            FROM DataConversion.dbo.School_plus_download_NI SP WHERE SP.URN=@urn

    END

ELSE

BEGIN

   alter table ditestdatabase.dbo.org_account disable trigger all   

    INSERT INTO ORG_ACCOUNT
    (ORGANISATION_REF,LY_TURNOVER,LY_PROFIT,LY_REMUN,LY_EMPLOYEES,PY_TURNOVER,PY_PROFIT,PY_REMUN,PY_EMPLOYEES,UK_TURNOVER,UK_PROFIT,UK_REMUN,UK_EMPLOYEES,CREATED_BY,
    CREATE_TIMESTAMP,UPDATED_BY,UPDATE_TIMESTAMP,PY_POT_SUB,LY_POT_SUB,LY_DATE,PY_DATE,UK_DATE)
    VALUES(@ORGREF,NULL,NULL,NULL,@Ly_employees,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,null,GETDATE(),null,null,null,null,null,null,null)

    set @orgref=(select organisation_ref from DiTestDatabase.dbo.ORG_ACCOUNT where ORGANISATION_REF=@ORGREF)

        end

        alter table ditestdatabase.dbo.org_account enable trigger all       







SELECT @iNextCodeRowId = NULL

--Get the next iRowId.
SELECT @iNextcodeRowId = MIN(iorgrowId)
FROM #orgs
WHERE iorgrowId > @iCurrentcodeRowId

--Did we get a valid next row id?
IF ISNULL(@iNextCodeRowId,0) = 0


BEGIN
BREAK
END

SELECT @iCurrentcodeRowId = iorgrowId,
@ORGREF=ORG_REF,
@Ly_employees=Total_Leader
FROM #orgs
WHERE iorgRowId = @iNextcodeRowId

END

DROP TABLE #orgs

RETURN

Solution :- As i dont have anything common between table a and c hence i have to take help of table b where the only common thing between a and b is column URN and the only column common between b and c is Org_ref. I have made the changes on my update as

SET LY_EMPLOYEES= (SELECT SP.TOTAL_LEADERSHIP_ROLE
            FROM DataConversion.dbo.School_plus_download_NI SP
            JOIN ORGANISATION ORG ON ORG.TOTAL_HEADCOUNT=SP.URN
            JOIN ORG_ACCOUNT OA ON OA.ORGANISATION_REF=ORG.ORGANISATION_REF
            WHERE sp.URN = @urn) --@ORGREF)

            FROM ORG_ACCOUNT WHERE ORGANISATION_REF=@ORG_REF

Which solved the issue.

Thanks all

The code is written in a very procedural programming manner and not a set based manner more suitable for databases. Abhishek is right to point you towards the `Merge` statement. In addition I think you need to explain / think over those BEGIN TRANSACTION lines, and when / where you intend to commit? The disabling of the trigger prior to insert and then enabling the trigger also seems suspicious. — Andrew, Aug 21 '18 at 09:45
@Andrew true, my back ground is core C# as an application developer that reflects on my query. i had to disable trigger to faster the process, each tables contains more than 3 triggers. — Biswa, Aug 21 '18 at 12:47
I had forgot i have 3 tables not two. i have updated my question please have a view my friends. — Biswa, Aug 21 '18 at 14:14
I am not even looking at that code you posted. As you said it doesn't work and using loops is just completely the wrong approach. Can you try to explain what you are trying to accomplish? — Sean Lange, Aug 21 '18 at 14:34
@SeanLange I have three tables. Table a ,b and c, where I have to update the column "total Employe" of table c based on the URN Key in table a. Table B doesnt have URN column but it has Org_ref which so to connect the table a But table C does not contain URN hence i cant join table a and C. I will join table a and b on urn than take the org_ref to make update/insert on table c — Biswa, Aug 21 '18 at 14:37
@SeanLange Hi Sean, im new to sql, any help on the approach would be much appricated — Biswa, Aug 21 '18 at 15:04
You have several examples posted here already. Try it out. You are going to learn a lot more by trying to do this then if somebody just hands you the code. — Sean Lange, Aug 21 '18 at 15:09
@SeanLange You cant use merge on this case. there is nothing common between table a and c , so you have to loop through with the help of table b in order to achieve this complicated process of update. — Biswa, Aug 22 '18 at 10:01
@Andrew its a complicated update, not an easy one where you find something common between the two table . so looping by setting a counter and taking the help of table b is the only way to solve this complicated update process. — Biswa, Aug 22 '18 at 10:02
@Abhishek sorry, i have changed my question . merge cant be use, its not an easy update, but far more complicated process. — Biswa, Aug 22 '18 at 10:03
I agree that MERGE may not be ideal in this situation but looping is not the best choice here. A simple update with a join would accommodate the first part. Then an insert using NOT EXISTS would capture the new rows. — Sean Lange, Aug 22 '18 at 13:52
@SeanLange can you show me how a simple update with a join will do this? i dont see any option other than looping, i would appreciate if you explain it with an example. — Biswa, Aug 22 '18 at 15:35
https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server/1604212#1604212 — Sean Lange, Aug 22 '18 at 15:36
@SeanLange it wont serve my purpose, as the script have to verify if the leadership number is correct in table c by comparing with a, if correct do not update, if incorrect , make update and if null make an update , if no org_ref make an insert. — Biswa, Aug 22 '18 at 15:39
Yeah you aren't going to do this in a single step. Read the comments I posted earlier. You FIRST update the data that needs to change. Then in a SECOND STEP you insert the new data using NOT EXISTS. — Sean Lange, Aug 22 '18 at 15:40
@SeanLange i wanted to do all at one process. which is much more resource effective. if i do the update and then insert for not exists it take 61 seconds on a 30000 records, while using the loop it took 29 second all together, now i have 1 billion row and we can't wast our system resource, we have to write query which is effective and get the upsert done in minimal amount of time. — Biswa, Aug 22 '18 at 15:47
I give up. You stated earlier your loop didn't work. Now you are saying it does. It was suggested you use MERGE but you don't want to do that. Then you got another suggestion and you stated how your previously not working code is faster. You seem to be in need of help but you are going around in circles. I sincerely hope you find the solution you are looking for. — Sean Lange, Aug 22 '18 at 15:51
@SeanLange I already found the solution, please check my post :) i have already put the resolution. — Biswa, Aug 22 '18 at 15:52

Sahi · Accepted Answer · 2018-08-23T04:29:49.850

is this helpful.?

    CREATE TABLE #a(urn bigint,  total_employe bigint)

    CREATE TABLE #b(urn bigint,org_ref bigint)

    CREATE TABLE #c(org_ref bigint,total_employe bigint)

    INSERT INTO #a
    SELECT 333,25 Union ALL
    SELECT 123,26 Union ALL
    SELECT 21,2 Union ALL
    SELECT 11,23




    INSERT INTO #b
    SELECT 333,1234 Union ALL
    SELECT 123,343 Union ALL
    SELECT 21,2 Union ALL
    SELECT 11,23 


    INSERT INTO #c
    SELECT 1234,0 Union ALL
    SELECT 343,0



    ;with cte
    AS
    (
    SELECT b.org_ref,a.total_employe from #a a
    INNER JOIN #b b on a.urn=b.urn
    )


    MERGE INTO #c c
    USING cte  on c.org_ref=cte.org_ref 
    WHEN MATCHED THEN
    UPDATE
    SET c.total_employe=cte.total_employe

    WHEN NOT MATCHED THEN
    INSERT(org_ref,total_employe)
    VALUES(org_ref,total_employe);

    SELECT * FROM #c


    DROP TABLE #a
    DROP TABLE #b
    DROP TABLE #c

i have made changes on my question, please have a look once again. — Biswa, Aug 21 '18 at 14:15

score 0 · Answer 2 · answered Aug 21 '18 at 09:44

Do not use a while loop or a cursor for a simple upsert operation! You will find everywhere on the web that it is counterproductive!

I will answer in the simpler terms of table a/b:

-- First, update the ones that exist
update alias_b -- Note that you NEED an alias to update using join
set [total employee]=a.[total employee]
from a 
inner join b alias_b on a.org_ref=alias_b.org_ref

-- Then, insert the ones that don't
insert b
select (the columns you want)
from a
where not exists (select 1 from b where a.org_ref=b.org_ref)

@geroge I have changed my question, i forgot to mentioned i have 3 table not 2. — Biswa, Aug 21 '18 at 14:15

score 0 · Answer 3 · answered Aug 21 '18 at 09:54

--UPDATE
UPDATE B SET B.TOTAL_LEADER = A.TOTAL_LEADER FROM TABLEA A INNER JOIN TABLEB B ON 
A.ORG_REF = B.ORG_REF
--INSERT
INSERT INTO TABLEB(ORG_REF,URN,TOTAL_LEADER)
SELECT A.ORG_REF,A.URN,A.TOTAL_LEADER FROM TABLEA A LEFT OUTER JOIN  TABLEB B ON 
A.ORG_REF = B.ORG_REF WHERE B.ORG_REF IS NULL

I Think you are looking for this one.

Lutz Kadoch · Answer 4 · 2018-08-21T15:04:16.873

0

How about an SELECT INTO? Something like:

SELECT b.org_ref, a.totalemployee INTO c FROM a LEFT JOIN b ON (a.urn = b.urn)

edited Aug 21 '18 at 15:04

answered Aug 21 '18 at 14:58

Lutz Kadoch

1
2

check my solution – Biswa Aug 22 '18 at 10:06

SQL Query to update or insert row with If Exists statement not working

4 Answers4