locked
Inset/Update when using UserDefinedTypes and sqltranstion in .Net RRS feed

  • Question

  • User592035958 posted

     I have userdefinedtypes which i use in stored procedure to perform transaction. and trying to insert/update set of users into db. i am using sqltransaction in .net while executing the database call. so when i try to add users the stored procedure should check if the user already exists and if exists it should update the existing user or add a new record. this is a very simple query am trying to execute. But when i try to add duplicates are inserted into table instead of updating. This happens only when i try to send the same user in same dataset.

    Stored Procedure

    CREATE PROCEDURE [dbo].[ManipulateUsers]
    @userType dbo.[userType] ReadOnly 
    AS
    BEGIN
    SET NOCOUNT ON; 
    
    UPDATE c 
    SET 
    address = c2.address,   
    phno = c2.phno
    FROM Users c
        JOIN @userType c2 on c2.firstname = c.firstname and c2.lastname = c.lastname;
    
    INSERT INTO Users(firstname,lastname, address,phno)
    SELECT c.firstname,c.lastname,c.address,c.phno
    FROM @userType c
    WHERE NOT EXISTS (SELECT 1 FROM Users WHERE firstname = c.firstname and lastname = c.lastname);
    END

    .Net Code

        using (var cmdProc = new SqlCommand(this.SPName, this.conn, transaction))
                        {
                            cmdProc.CommandType = CommandType.StoredProcedure;
                            cmdProc.Parameters.AddWithValue("@" + userdefinedtype, dt);
                            cmdProc.ExecuteNonQuery();
                        }
    
    Table
    firstname lastname  address     phno
    Bill       gates     sakfnak     25863145
    Bill       stone     dafafasf    2541545
    Nina       Higgens   rhakkjfsl   6561323
    Bill       gates     agadsfs     6456132

    So here it should add only 3 records. when it comes to 4th one it should update the 1st record.

    Tuesday, July 19, 2016 1:29 PM

Answers

  • User753101303 posted

    Ah I meant :

    SELECT main.* FROM @userType AS Main
     JOIN (SELECT MAX(LineId) LineId FROM @userType GROUP BY username,lastname) dedup ON dedup.LineId=Main.LineId

    So I just have a query that gives the max lineid for each username,lastname couple (that is the last change) and I join that to the main table based on this id to keep all the information on those last changes.

    Then you should be able to use that to update/or insert into your own tables...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 28, 2016 5:32 PM

All replies

  • User-286291038 posted

    Hi srrr,

    Why not check if not exists and then insert your user data. Something along the lines of,

    IF (NOT EXISTS(SELECT 1 FROM Users WHERE firstname = c.firstname and lastname = c.lastname))

    BEGIN
    [Insert into your user table]
    END
    ELSE
    BEGIN
    [Update your user table]
    END

    Tuesday, July 19, 2016 3:05 PM
  • User592035958 posted

    I did try this already. it inserts only the record with is not duplicate in the datatable. For example if i pass there records User1 , user2 and user1 again with a update then it is inserting only User2 and ignoring the user1. Below is my if. please see if it is correct

    IF (NOT EXISTS(SELECT 1 FROM Users c left join @usertype c2 on c2.firstname = c.firstname and c2.lastname = c.lastname WHERE c2.firstname = c.firstname and c2.lastname = c.lastname))
    
    begin 
    insert
    end
    else
    begin
    update
    end


    Tuesday, July 19, 2016 5:25 PM
  • User-286291038 posted

    Hi,

    It should actually go to the else part and do the update.

    Also, you can try to add another if condition (If exists (condtion) then do your update). Write the if not exists condition after this.

    Tuesday, July 19, 2016 7:09 PM
  • User592035958 posted

    i have tried it, it is just ignoring the duplicates below is my code and output

    Declare @p1 dbo.Users
    
    insert into @p1 values('bill','gates',address','398174')
    
    insert into @p1 values('bill','stone',139 address','532522')
    
    insert into @p1 values('bill','gates',30 adyerg','146542')
    
    IF (EXISTS(SELECT 1 FROM Users c left join @p1c2 on c2.firstname = c.firstname and c2.lastname = c.lastname WHERE c2.firstname = c.firstname and c2.lastname = c.lastname))
    BEGIN 
    print 'true'
    END 
    
    IF (NOT EXISTS(SELECT 1 FROM Users c left join @p1  c2 on c2.firstname = c.firstname and c2.lastname = c.lastname WHERE c2.firstname = c.firstname and c2.lastname = c.lastname))
    
    BEGIN 
    print 'false'
    END

    Output was

    (1 row(s) affected)
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    true
    
    (1 row(s) affected)
    

    Just the bill stone was inserted :( . I did try using the MERGE 

    Tuesday, July 19, 2016 7:17 PM
  • User-1404113929 posted

    hi,

    can you try bellow code.

    Merge [dbo].users as t Using
    (
    select firstname,lastname,address,phno From @userType
    ) s on s.firstname = t.firstname And s.lastname = t.lastname 
    When matched s.firstname != t.firstname And s.lastname != t.lastname Then 
    update set  t.firstname = s.firstname ,t.lastname = s.lastname,t.address=s.address,t.phno =s.phno 
    When not matched Then 
    insert (firstname,lastname,address,phno)
    values(s.firstname,s.lastname,s.address,s.phno);

    thanks,

    murali

    Wednesday, July 20, 2016 7:08 AM
  • User592035958 posted

    I have tried that as well. i see the problem happens when i pass same users in one batch. but if i pass the user that already exists in the table it updates without any issue. Do i need to write any special code for sending sames new users in one batch

    Wednesday, July 20, 2016 1:08 PM
  • User592035958 posted

    Any ideas anyone??

    Wednesday, July 20, 2016 8:22 PM
  • User753101303 posted

    Hi,

    So it seems you understood that your issue likely happens when you have duplicates in @userType (so you have no existing match and then you insert those duplicates).

    Susggesting a fix is not easy as it depends on what you want to do (for example if you have the same name two times with two distinct addresses). Also IMO you have anyway a design issue. What if you really have two people named the same way? Or if a woman changes its last name?

    Not sure from where you got those data but if possible I would suggest that they expose some kind of unique id that you could also keep on your side and that would allow to better synchronize your db with further updates...

    Wednesday, July 20, 2016 10:50 PM
  • User592035958 posted

    Hi PatriceSc,

    This is not real time data.i have just gave an example for my scenario. In my case the dataset(input data) can contain duplicates meaning, the dataset has a record and it can have same record(with same unique id(here same first and last name)) with updates to the old record. so when i see such record first i have to add that record and where i see a duplicate record i have to update that record. Hope this is clear 

    Thursday, July 21, 2016 1:47 PM
  • User753101303 posted

    And they are ordered in the dataset? So if you have duplicates (you can't filter them out of the source, basically your input file is an history of all changes rather than just a current state ?), my understanding is that you want to keep the last one. You'll likely need a chronological column to do that. My approach would be something such as (syntax not tested, this is to give the principle).

    SELECT main.* FROM @userType
    JOIN (SELECT MAX(LineId) LineId FROM @userType GROUP BY username,lastname) dedup ON dedup.LineId=Main.LineId

    So in short, LineId being a unique, chronological line identifier, I compute the last line id for each (username,lastname) pair to keep only those rows for the final update/insert query.

    Thursday, July 21, 2016 3:49 PM
  • User592035958 posted
     (you can't filter them out of the source, basically your input file is an history of all changes rather than just a current state ?)

    this is what i want to do. 

    i am trying to implement your suggestion. i got what is lineID(it is some unique id )  here but am not able to undestand  "main.*" in your query. can you please explain.

    Friday, July 22, 2016 5:45 PM
  • User753101303 posted

    Ah I meant :

    SELECT main.* FROM @userType AS Main
     JOIN (SELECT MAX(LineId) LineId FROM @userType GROUP BY username,lastname) dedup ON dedup.LineId=Main.LineId

    So I just have a query that gives the max lineid for each username,lastname couple (that is the last change) and I join that to the main table based on this id to keep all the information on those last changes.

    Then you should be able to use that to update/or insert into your own tables...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 28, 2016 5:32 PM
  • User-157633207 posted

    Hi srr,

    Try this it will work for you..

    MERGE INTO dbo.Users u
    USING
    (
    	SELECT * FROM @userType
    ) temp 
    ON u.firstname = temp.firstname
       AND u.lastname = temp.lastname
    WHEN NOT MATCHED 
    THEN INSERT
    (
         firstname,
         lastname,
         address,
         phno
    )
    VALUES
    (
         temp.firstname,
         temp.lastname,
         temp.address,
         temp.phno
    )
    WHEN MATCHED THEN 
    UPDATE SET 
               address = temp.address,
               phno = temp.phno;

    Thanks..

    Saturday, July 30, 2016 11:46 AM
  • User592035958 posted

    Awesome.. This worked perfectly

    Tuesday, August 9, 2016 6:18 PM