Safa Insert

  • Hello, i'm looking a way to make safe insert in a table avoiding PK problems.

    I have a table like this

    CREATE TABLE [dbo].[Passaggi](

    [Code] [varchar](6) NOT NULL,

    [DataPassaggio] [datetime2](7) NOT NULL,

    [idServizio] [smallint] NOT NULL,

    CONSTRAINT [PK_Passaggi] PRIMARY KEY CLUSTERED

    (

    [Code] ASC,

    [DataPassaggio] ASC,

    [idServizio] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    After that, i use this query for safe insert.

    I want to insert the record or do nothing if it is already present.

    DECLARE @Code varchar(6)

    DECLARE @DataPassaggio datetime2(0)

    DECLARE @idServizio smallint

    SET @Code = 'xyz123'

    SET @DataPassaggio = GETDATE()

    SET @idServizio = 1

    INSERT INTO Passaggi(Code, DataPassaggio, idServizio)

    SELECT @Code, @DataPassaggio, @idServizio

    WHERE NOT EXISTS (SELECT Code FROM Passaggi WITH(UPDLOCK, HOLDLOCK) WHERE Code = @Code AND idServizio = @idServizio AND DataPassaggio = @DataPassaggio)

    you chan change SET @DataPassaggio = GETDATE() to SET  @DataPassaggio = '20230123 09:50' for highlight the problem (or execute fast)

    In sql Managment studio all works very well, sometimes i have 1 insert, sometimes 0 insert (right), but no erros.

    When i invoke this command from ado.net (.net framework 4.6), simple ado.net (not entity framework) , web app change statement in

    exec sp_executesql N'INSERT INTO Passaggi(Code, DataPassaggio, idServizio) ....'

    and when execute i have an error.

    I find out with sql Profiler

    Also in Sql Managment studio if query start with exec sp_executesql N'INSERT INTO Passaggi(Code, DataPassaggio, idServizio) ....'

    i have an error

    how can i fix it ? thanks

     

     

     

     

     

     

     

     

  • If you get an error, what is that error?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Violazione del vincolo PRIMARY KEY 'PK_Passaggi'. Impossibile inserire la chiave duplicata nell'oggetto 'dbo.Passaggi'. Valore della chiave duplicata: (yz4zmt, 262, 2023-01-23 09:51:14)

    In english is "Violation of PRIMARY KEY constraint 'PK_Passaggi'. Could not insert duplicate key into object ....."

    But if i use this query in Enterprise Manager, i never have this error  (only record inserter 1 or 0, but no errors)

    INSERT INTO Passaggi(Code, DataPassaggio, idServizio)

    SELECT @Code, @DataPassaggio, @idServizio

    WHERE NOT EXISTS (SELECT Code FROM Passaggi WITH(UPDLOCK, HOLDLOCK) WHERE Code = @Code AND idServizio = @idServizio AND DataPassaggio = @DataPassaggio)

     

     

     

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

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