ЦЕННОСТИ ВСТАВКИ, НЕ СУЩЕСТВУЮЩИЕ
ОК, поэтому я пытаюсь улучшить страницу ввода данных asp, чтобы убедиться, что запись в моей таблице данных уникальна.
Итак, в этой таблице есть SoftwareName и SoftwareType. Я пытаюсь получить это так, если страница входа отправляет запрос вставки с параметрами, которые соответствуют тем, что в таблице (так же, что и название и тип), затем возникает ошибка и данные не вводятся.
Что-то вроде этого:
INSERT INTO tblSoftwareTitles(
SoftwareName,
SoftwareSystemType)
VALUES(@SoftwareName,@SoftwareType)
WHERE NOT EXISTS (SELECT SoftwareName
FROM tblSoftwareTitles
WHERE Softwarename = @SoftwareName
AND SoftwareType = @Softwaretype)
Итак, этот синтаксис отлично подходит для выбора столбцов из одной таблицы в другую без ввода дубликатов, но, похоже, не работает с параметризованным вставным запросом. может ли кто-нибудь помочь мне с этим?
Cheers Dan
Edit:
Здесь код, который я использую в моем методе вставки asp
private void ExecuteInsert(string name, string type)
{
//Creates a new connection using the HWM string
using (SqlConnection HWM = new SqlConnection(GetConnectionStringHWM()))
{
//Creates a sql string with parameters
string sql = " INSERT INTO tblSoftwareTitles( "
+ " SoftwareName, "
+ " SoftwareSystemType) "
+ " SELECT "
+ " @SoftwareName, "
+ " @SoftwareType "
+ " WHERE NOT EXISTS "
+ " ( SELECT 1 "
+ " FROM tblSoftwareTitles "
+ " WHERE Softwarename = @SoftwareName "
+ " AND SoftwareSystemType = @Softwaretype); ";
//Opens the connection
HWM.Open();
try
{
//Creates a Sql command
using (SqlCommand addSoftware = new SqlCommand{
CommandType = CommandType.Text,
Connection = HWM,
CommandTimeout = 300,
CommandText = sql})
{
//adds parameters to the Sql command
addSoftware.Parameters.Add("@SoftwareName", SqlDbType.NVarChar, 200).Value = name;
addSoftware.Parameters.Add("@SoftwareType", SqlDbType.Int).Value = type;
//Executes the Sql
addSoftware.ExecuteNonQuery();
}
Alert.Show("Software title saved!");
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
}
}
Ответы
Ответ 1
Я бы сделал это с помощью инструкции IF:
IF NOT EXISTS
( SELECT 1
FROM tblSoftwareTitles
WHERE Softwarename = @SoftwareName
AND SoftwareSystemType = @Softwaretype
)
BEGIN
INSERT tblSoftwareTitles (SoftwareName, SoftwareSystemType)
VALUES (@SoftwareName, @SoftwareType)
END;
Вы можете сделать это без IF с помощью SELECT
INSERT tblSoftwareTitles (SoftwareName, SoftwareSystemType)
SELECT @SoftwareName,@SoftwareType
WHERE NOT EXISTS
( SELECT 1
FROM tblSoftwareTitles
WHERE Softwarename = @SoftwareName
AND SoftwareSystemType = @Softwaretype
);
Оба метода восприимчивы к условию , поэтому, хотя я бы по-прежнему использовал один из указанных выше для вставки, но вы можете защитить дубликаты вставок с уникальным ограничением:
CREATE UNIQUE NONCLUSTERED INDEX UQ_tblSoftwareTitles_Softwarename_SoftwareSystemType
ON tblSoftwareTitles (SoftwareName, SoftwareSystemType);
Пример SQL-скрипта
ДОПОЛНЕНИЕ
В SQL Server 2008 или более поздней версии вы можете использовать MERGE с HOLDLOCK, чтобы удалить вероятность состояния гонки (которая по-прежнему не является заменой уникального ограничения).
MERGE tblSoftwareTitles WITH (HOLDLOCK) AS t
USING (VALUES (@SoftwareName, @SoftwareType)) AS s (SoftwareName, SoftwareSystemType)
ON s.Softwarename = t.SoftwareName
AND s.SoftwareSystemType = t.SoftwareSystemType
WHEN NOT MATCHED BY TARGET THEN
INSERT (SoftwareName, SoftwareSystemType)
VALUES (s.SoftwareName, s.SoftwareSystemType);
Пример слияния по скрипту SQL
Ответ 2
Это не ответ. Я просто хочу показать, что метод IF NOT EXISTS(...) INSERT не безопасен. Вы должны выполнить сначала Session #1, а затем Session #2. После v #2 вы увидите, что без индекса UNIQUE вы можете получить повторяющиеся пары (SoftwareName,SoftwareSystemType). Задержка с сеанса # 1 используется, чтобы дать вам достаточно времени для выполнения второго script (сеанС# 2). Вы можете уменьшить эту задержку.
Сессия № 1 (SSMS > Новый запроs > F5 (Выполнение))
CREATE DATABASE DemoEXISTS;
GO
USE DemoEXISTS;
GO
CREATE TABLE dbo.Software(
SoftwareID INT PRIMARY KEY,
SoftwareName NCHAR(400) NOT NULL,
SoftwareSystemType NVARCHAR(50) NOT NULL
);
GO
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (1,'Dynamics AX 2009','ERP');
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (2,'Dynamics NAV 2009','SCM');
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (3,'Dynamics CRM 2011','CRM');
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (4,'Dynamics CRM 2013','CRM');
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (5,'Dynamics CRM 2015','CRM');
GO
/*
CREATE UNIQUE INDEX IUN_Software_SoftwareName_SoftareSystemType
ON dbo.Software(SoftwareName,SoftwareSystemType);
GO
*/
-- Session #1
BEGIN TRANSACTION;
UPDATE dbo.Software
SET SoftwareName='Dynamics CRM',
SoftwareSystemType='CRM'
WHERE SoftwareID=5;
WAITFOR DELAY '00:00:15' -- 15 seconds delay; you have less than 15 seconds to switch SSMS window to session #2
UPDATE dbo.Software
SET SoftwareName='Dynamics AX',
SoftwareSystemType='ERP'
WHERE SoftwareID=1;
COMMIT
--ROLLBACK
PRINT 'Session #1 results:';
SELECT *
FROM dbo.Software;
Сессия № 2 (SSMS > Новый запроs > F5 (Выполнение))
USE DemoEXISTS;
GO
-- Session #2
DECLARE
@SoftwareName NVARCHAR(100),
@SoftwareSystemType NVARCHAR(50);
SELECT
@SoftwareName=N'Dynamics AX',
@SoftwareSystemType=N'ERP';
PRINT 'Session #2 results:';
IF NOT EXISTS(SELECT *
FROM dbo.Software s
WHERE [email protected]
AND [email protected])
BEGIN
PRINT 'Session #2: INSERT';
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (6,@SoftwareName,@SoftwareSystemType);
END
PRINT 'Session #2: FINISH';
SELECT *
FROM dbo.Software;
Результаты:
Session #1 results:
SoftwareID SoftwareName SoftwareSystemType
----------- ----------------- ------------------
1 Dynamics AX ERP
2 Dynamics NAV 2009 SCM
3 Dynamics CRM 2011 CRM
4 Dynamics CRM 2013 CRM
5 Dynamics CRM CRM
Session #2 results:
Session #2: INSERT
Session #2: FINISH
SoftwareID SoftwareName SoftwareSystemType
----------- ----------------- ------------------
1 Dynamics AX ERP <-- duplicate (row updated by session #1)
2 Dynamics NAV 2009 SCM
3 Dynamics CRM 2011 CRM
4 Dynamics CRM 2013 CRM
5 Dynamics CRM CRM
6 Dynamics AX ERP <-- duplicate (row inserted by session #2)
Ответ 3
Существует большое решение этой проблемы, вы можете использовать Merge Keyword Sql
Merge MyTargetTable hba
USING (SELECT Id = 8, Name = 'Product Listing Message') temp
ON temp.Id = hba.Id
WHEN NOT matched THEN
INSERT (Id, Name) VALUES (temp.Id, temp.Name);
Вы можете проверить это, прежде чем следовать ниже, ниже пример
IF OBJECT_ID ('dbo.TargetTable') IS NOT NULL
DROP TABLE dbo.TargetTable
GO
CREATE TABLE dbo.TargetTable
(
Id INT NOT NULL,
Name VARCHAR (255) NOT NULL,
CONSTRAINT PK_TargetTable PRIMARY KEY (Id)
)
GO
INSERT INTO dbo.TargetTable (Name)
VALUES ('Unknown')
GO
INSERT INTO dbo.TargetTable (Name)
VALUES ('Mapping')
GO
INSERT INTO dbo.TargetTable (Name)
VALUES ('Update')
GO
INSERT INTO dbo.TargetTable (Name)
VALUES ('Message')
GO
INSERT INTO dbo.TargetTable (Name)
VALUES ('Switch')
GO
INSERT INTO dbo.TargetTable (Name)
VALUES ('Unmatched')
GO
INSERT INTO dbo.TargetTable (Name)
VALUES ('ProductMessage')
GO
Merge MyTargetTable hba
USING (SELECT Id = 8, Name = 'Listing Message') temp
ON temp.Id = hba.Id
WHEN NOT matched THEN
INSERT (Id, Name) VALUES (temp.Id, temp.Name);
Ответ 4
Дополнительная ссылка для комментариев для дальнейшего чтения... Очень хорошая статья в блоге, в которой сравниваются различные способы выполнения этой задачи можно найти здесь.
Они используют несколько методов: "Вставить, где не существует", "Объединить", "Вставить исключение" и ваше типичное "левое соединение", чтобы узнать, какой путь является самым быстрым для выполнения этой задачи.
Пример кода, используемого для каждого метода, выглядит следующим образом (прямая копия/вставка со своей страницы):
INSERT INTO #table1 (Id, guidd, TimeAdded, ExtraData)
SELECT Id, guidd, TimeAdded, ExtraData
FROM #table2
WHERE NOT EXISTS (Select Id, guidd From #table1 WHERE #table1.id = #table2.id)
-----------------------------------
MERGE #table1 as [Target]
USING (select Id, guidd, TimeAdded, ExtraData from #table2) as [Source]
(id, guidd, TimeAdded, ExtraData)
on [Target].id =[Source].id
WHEN NOT MATCHED THEN
INSERT (id, guidd, TimeAdded, ExtraData)
VALUES ([Source].id, [Source].guidd, [Source].TimeAdded, [Source].ExtraData);
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT id, guidd, TimeAdded, ExtraData from #table2
EXCEPT
SELECT id, guidd, TimeAdded, ExtraData from #table1
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT #table2.id, #table2.guidd, #table2.TimeAdded, #table2.ExtraData
FROM #table2
LEFT JOIN #table1 on #table1.id = #table2.id
WHERE #table1.id is null
Это хорошо читать для тех, кто ищет скорость! В SQL 2014 метод Insert-Except оказался самым быстрым для 50 миллионов или более записей.
Ответ 5
Игнорирование дублированного уникального ограничения не является решением?
INSERT IGNORE INTO tblSoftwareTitles...
