insert multiple times...

  • Ok thought of something else.

    If any of the following values are inserted as null then you won't find a duplicate.

    @Name AS VARCHAR(4000),

    @Phone AS VARCHAR(4000),

    @Email AS VARCHAR(4000),

    @Obs AS VARCHAR(4000),

    @EstateID AS INT

    Might be work using ISNULL on the params and column values in the proc [CentralCheckDuplicate]

    does that make sense.

    Other than that I can't see anything else unless I see data

    for example what the actually duplicate records look like and what is passed to the main procedure that does the insert.

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • he he he

    sorry for all the replies.

    Another possible problem could this:

    Because you checking for the duplicates in another proc and not on the insert statement.

    There is a chance that a row could be added between you checking for the duplicate and adding your row.

    It might be worth making your insert statment look like this:

    [font="Courier New"]INSERT INTO Contacts

            (

                    Form,                

                    Name,

                    Phone,

                    Email,

                    Obs,

                    Business,

                    Nature,

                    PropType,

                    Towns,

                    Neighborhood,

                    RealEstate_ID,

                    BPI,

                    MaxPrice,

                    fromWhere,

                    UserIP,

                    Lang,

                    FoundInPage,

                    TotalPages,

                    Estate,

                    Portal,

                    Origin,

                    ClientID,

                    Blocked

            )

    SELECT TOP 1

                    @Form,

                    @Name,

                    @Phone,

                    @Email,

                    @Obs,

                    @BusinessCLID,

                    @NatureCLID,

                    @ProptypeCLID,                

                    @Town,

                    @Neighborhood,

                    @EstateID,

                    @BPI,

                    @MaxPrice,

                    @fromWhere,

                    @UserIP,

                    @Lang,

                    @FoundInPage,

                    @TotalPages,

                    @EstateCLID,

                    @Portal,

                    @Origin,

                    @ClientID,

                    @BadText

    FROM dbo.CONTACTS

    WHERE

       ISNULL([Realestate_id],0) !=ISNULL(@EstateID ,0)

    AND ISNULL(LEN(@OBS),0) != ISNULL(LEN(OBS),0)

    AND ISNULL([NAME],'') NOT LIKE ISNULL(@Name,'') AND ISNULL([Phone],'') NOT LIKE  ISNULL(@Phone ,'')

    AND ISNULL([Email],'') NOT LIKE ISNULL(@Email,'') AND ISNULL([OBS],'') NOT LIKE ISNULL(@Obs ,'')[/font]

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Hello again,

    no worries for the replies... ehehe, i was looking, and no parameter can be null, the maximum is '' 🙂

    I understand what u said on the last reply, but if the check is in the middle of a transaction, shouldnt be a problem right?

  • i put the profiler running and i catch another duplicated insert...

    declare @p21 int

    set @p21=1

    declare @p22 int

    set @p22=4135805

    exec CentralSaveContactPoint @form=4,@Name=N'José XXXX',@Phone=N'xxxxxxx',@Email=N'jxxxxxxx9@gmail.com',@Obs=N'comments',@Portal=1,@Origin=0,@EstateID=741636,@Business=0,@Nature=0,@PropType=0,@Town=0,@MaxPrice=0,@ClientID=0,@BPI=0,@Lang=0,@FoundInPage=0,@TotalPages=0,@FromWhere=1,@UserIP=N'00.000.000.00',@RowCount=@p21 output,@ScopeIdentity=@p22 output

    select @p21, @p22

    go

    declare @p21 int

    set @p21=1

    declare @p22 int

    set @p22=4135806

    exec CentralSaveContactPoint @form=4,@Name=N'José XXXX',@Phone=N'xxxxxxx',@Email=N'jxxxxxxx9@gmail.com',@Obs=N'comments',@Portal=1,@Origin=0,@EstateID=741636,@Business=0,@Nature=0,@PropType=0,@Town=0,@MaxPrice=0,@ClientID=0,@BPI=0,@Lang=0,@FoundInPage=0,@TotalPages=0,@FromWhere=1,@UserIP=N'00.000.000.00',@RowCount=@p21 output,@ScopeIdentity=@p22 output

    select @p21, @p22

    go

  • ooops.

    Could you edit your post and put line returns in as the screen is now left-right scrollable.

    Also have tried running that code after changing the insert statement to see what happens?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • yes i try with the altered insert... but same thing occours...

    declare @p21 int

    set @p21=1

    declare @p22 int

    set @p22=4135805

    exec CentralSaveContactPoint @form=4,@Name=N'José XXXX',@Phone=N'xxxxxxx',

    @Email=N'jxxxxxxx9@gmail.com',@Obs=N'comments',@Portal=1,@Origin=0,@EstateID=741636,

    @Business=0,@Nature=0,@PropType=0,@Town=0,@MaxPrice=0,@ClientID=0,@BPI=0,@Lang=0,

    @FoundInPage=0,@TotalPages=0,@FromWhere=1,@UserIP=N'00.000.000.00',

    @RowCount=@p21 output,@ScopeIdentity=@p22 output

    select @p21, @p22

    go

    declare @p21 int

    set @p21=1

    declare @p22 int

    set @p22=4135806

    exec CentralSaveContactPoint @form=4,@Name=N'José XXXX',@Phone=N'xxxxxxx',

    @Email=N'jxxxxxxx9@gmail.com',@Obs=N'comments',@Portal=1,@Origin=0,@EstateID=741636,

    @Business=0,@Nature=0,@PropType=0,@Town=0,@MaxPrice=0,@ClientID=0,@BPI=0,

    @Lang=0,@FoundInPage=0,@TotalPages=0,@FromWhere=1,@UserIP=N'00.000.000.00',

    @RowCount=@p21 output,@ScopeIdentity=@p22 output

    select @p21, @p22

    go

  • Sorry my change to your insert won't work

    You need to make the where clause on the insert a NOT EXISTS(SELECT id from mytable WHERE field1 = @field1)

    etc

    let me know if that works

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Before i test ur new tip ( and i have to thanks again, for dont give up 🙂 ), we put the begin trans in the beginiing of the procedure... 25 min ago and no duplicate rows until now 🙂

  • Does your test created duplicates with the transactions in place?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Hi again,

    How are u since yesterday 🙂

    With the trans in the places the duplicate rows still appear...

  • Hi again,

    How are u since yesterday 🙂

    With the trans in the places the duplicate rows still appear...

  • I forgot which thread had a similar problem but here'S something else to think about...

    Even with a transaction if you run your code like this :

    begin tran

    if not exists (Check if exists)

    begin

    insert into...

    end

    commit tran

    There's a tiny delay, just long enough where a second call to the sp could be started and run right in between the moment where the if is evaluated and the insert into is executed.

    Jeff Moden or Sergiy had created a test case for this which includes opening a few query windows in QA. It goes something like this :

    goto

    q1 :

    Begin tran

    if not exists ()

    begin

    -- do nothing

    end

    goto

    q2 insert duplicate row and COMMIT

    goto

    q1 run the same insert as q2 and try COMMIT >> that'll throw an error (or insert a duplicate depending on the DRI).

    The solution to this problem was quite simple, change the statement to this :

    begin tran

    insert into table (columns) Select columns, parameters where not exists (check exists on base table here)

    commit tran

    In that case, the check and the insert are run at the exact same time, while there's a lock on the row, so nothing else can get by.

    Now the final question could be this, why isn't there any DRI on the table to block duplicates (wheter in unique constraints, check, or even a trigger depending on the complexity)?

    I think with those 2 solutions, you should avoid that problem once and for all!

    Hope this helps.

  • Just on top of my head... a unique constraint of the e-mail could be a great place to start.

    Of course since basically all the columns can be nullable in the insert, it's kind of hard to pick a single perfect spot to block duplicates!!

    Let me know if you have any more questions after you solve this!

  • IS this working with the change to the insert statement yet?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Hello again,

    First to Ninja's_RGR'us:

    We try this

    begin tran

    insert into table (columns) Select columns, parameters where not exists (check exists on base table here)

    commit tran

    and the same thing happens, few less times, but happens...

    About the check constraints, is impossible do that, cause of the flow of the business.. in this table we save the contacts from potencial clients or clients, and this table can have hundread times the same contact, but always with different GETDATE(), so is not good political like u example, to check constraint to email field or phone field...

    Regards,

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply