January 23, 2023 at 9:09 am
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 23, 2023 at 10:32 am
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)
January 24, 2023 at 10:37 am
This was removed by the editor as SPAM
January 30, 2023 at 7:34 am
You will need to write code that can trap the duplicate insert condition and take the action you desire in this situation. Typically the code you need will be packaged in a stored procedure and use try/catch logic.
Any code based on trying to detect if the key is already in use prior to doing the insert will fail if the number of inserts each second is high enough. There is no way in SQL Server to hold a lock on a non-existant object. Also the portions of any SQL statement get decomposed into separate executable units and scheduled independently.
This means that if insert rate is high enough two separate inserts could both be running at effectively the same time. Both could detect that the desired key is absent but the insert that gets processed second would fail on a duplicate key.
If the inserts come from an OLTP system it is good to have code that checks for potential errors and warns the user before attempting the insert (or whatever else may cause a problem). However, you should always design you code to run at scale, and part of this means trapping and dealing with errors after they get reported by SQL Server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 30, 2023 at 7:40 am
I forgot to say in my previous post, please do not use locking hints. These very seldom give any benefits but very often harm performance. Always write code that assumes an infinite number of simultaneous users who will all demand the same level of performance. Locking hints and high scale do not play well together.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 30, 2023 at 5:23 pm
Thank you for your anwser.
After some searching i found the error
I use this statement
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)
and I will take your advice into consideration
from ado.net the problem was
sqlCmd.Parameters.Add("@DataPassaggio", SqlDbType.DateTime2, 0).Value = DateTime.Now
replaced with
DateTime date = DateTime.Now;
sqlCmd.Parameters.Add("@DataPassaggio", SqlDbType.DateTime2, 0).Value = new DateTime(date.Year, date.Month, date.Day, date.Hour, date.Minute, date.Second, date.Kind);
I thought using datetime2 the date would be truncated to seconds
Try this with Managment studio
1 find a table with field datetime2(0), suppose have this value 2017-02-14 08:49:13
now try
declare @datamarcata datetime2
set @datamarcata = '20170214 08:49:13.97878' -- add some milliseconds
select * from marcate where DataMarcata = @DataMarcata -- no rows
set @datamarcata = '20170214 08:49:13'
select * from marcate where DataMarcata = @DataMarcata -- rows found
(Excuse me for my english)
January 30, 2023 at 7:32 pm
CREATE TABLE Passaggi
(foobar_code VARCHAR(6) NOT NULL,
passagggio_timestamp DATETIME2(7) DEFAULT CURRENT TIMESTAMP NOT NULL,
PRIMARY KEY(foobar_code, passagggio_timestamp),
servizio_id SMALLINT NOT NULL);
>> I want to insert the record [sic: rows are not records] or do nothing if it is already present. <<
If you try to insert a duplicate row, and the key is already there, then you will get an error and the insertion will be rejected. I tried to convert your dialect into ANSI/ISO standard SQL, but left some of the dialect in place. The original Sybase GETDATE() was replaced with the ANSI/ISO standard CURRENT_TIMESTAMP.
There is no such thing as a universal, magic, generic "code" in a properly designed system. It has to be some kind of code. Perhaps postal? Perhaps an internal product code? Remember the law of identity from your first course in logic – "to be is to be something in particular; to be something in general or nothing in particular or anything in general, is to be nothing at all."
There is no need to create local variables unless you just like watching the compiler allocate storage.
INSERT INTO Passaggi(Code,passagggio_timestamp,servizio_id)
VALUES ('xyz123', CURRENT_TIMESTAMP, 1);
>> .. and when execute I have an error.<<
What is the error? I am assuming it's a duplicate primary key, but I can't read your mind.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 30, 2023 at 7:32 pm
CREATE TABLE Passaggi
(foobar_code VARCHAR(6) NOT NULL,
passagggio_timestamp DATETIME2(7) DEFAULT CURRENT TIMESTAMP NOT NULL,
PRIMARY KEY(foobar_code, passagggio_timestamp),
servizio_id SMALLINT NOT NULL);
>> I want to insert the record [sic: rows are not records] or do nothing if it is already present. <<
If you try to insert a duplicate row, and the key is already there, then you will get an error and the insertion will be rejected. I tried to convert your dialect into ANSI/ISO standard SQL, but left some of the dialect in place. The original Sybase GETDATE() was replaced with the ANSI/ISO standard CURRENT_TIMESTAMP.
There is no such thing as a universal, magic, generic "code" in a properly designed system. It has to be some kind of code. Perhaps postal? Perhaps an internal product code? Remember the law of identity from your first course in logic – "to be is to be something in particular; to be something in general or nothing in particular or anything in general, is to be nothing at all."
There is no need to create local variables unless you just like watching the compiler allocate storage.
INSERT INTO Passaggi(Code,passagggio_timestamp,servizio_id)
VALUES ('xyz123', CURRENT_TIMESTAMP, 1);
>> .. and when execute I have an error.<<
What is the error? I am assuming it's a duplicate primary key, but I can't read your mind.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 30, 2023 at 7:46 pm
If you paste the code into a "code" window it will be formatted:
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)
);
--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);
I wouldn't use WITH (UPDLOCK, HOLDLOCK)
If you use SET @DataPassaggio = GETDATE() it is very unlikely that this row will exist in the table already. So I would remove that from the exists condition:
INSERT INTO Passaggi
(
Code,
DataPassaggio,
idServizio
)
SELECT @Code,
@DataPassaggio,
@idServizio
WHERE NOT EXISTS (SELECT Code
FROM Passaggi
WHERE Code = @Code
AND idServizio = @idServizio)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply