Safa Insert

Question

Post reply

Safa Insert

Alessandro Andreatta

Old Hand

Points: 340
More actions
January 23, 2023 at 9:09 am

#4140689

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
January 24, 2023 at 10:37 am

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

Thom A SSC Guru Points: 99038 More actions · Answer 1

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

Alessandro Andreatta Old Hand Points: 340 More actions · Answer 2

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)