sql - postgres - INSERTE VALORES DONDE NO EXISTE
mysql insert values where not exists (5)
De acuerdo, estoy intentando mejorar la página de entrada de datos de mi asp para asegurar que la entrada que va a mi tabla de datos sea única.
Así que en esta tabla tengo SoftwareName y SoftwareType. Estoy intentando obtenerlo, por lo que si la página de entrada envía una consulta de inserción con parámetros que coinciden con lo que hay en la tabla (el mismo título y tipo), se genera un error y no se ingresan los datos.
Algo como esto:
INSERT INTO tblSoftwareTitles(
SoftwareName,
SoftwareSystemType)
VALUES(@SoftwareName,@SoftwareType)
WHERE NOT EXISTS (SELECT SoftwareName
FROM tblSoftwareTitles
WHERE Softwarename = @SoftwareName
AND SoftwareType = @Softwaretype)
Entonces, esta sintaxis funciona muy bien para seleccionar columnas de una tabla a otra sin que se ingresen duplicados, pero no parece querer trabajar con una consulta de inserción parametrizada. ¿Puede alguien ayudarme con esto?
Editar:
Aquí está el código que estoy usando en mi método de inserción 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);
}
}
}
¿Ingnorar la restricción única duplicada no es una solución?
INSERT IGNORE INTO tblSoftwareTitles...
Haría esto usando y una declaración IF :
IF NOT EXISTS
( SELECT 1
FROM tblSoftwareTitles
WHERE Softwarename = @SoftwareName
AND SoftwareSystemType = @Softwaretype
)
BEGIN
INSERT tblSoftwareTitles (SoftwareName, SoftwareSystemType)
VALUES (@SoftwareName, @SoftwareType)
END;
Podrías hacerlo sin IF usando SELECT
INSERT tblSoftwareTitles (SoftwareName, SoftwareSystemType)
SELECT @SoftwareName,@SoftwareType
WHERE NOT EXISTS
( SELECT 1
FROM tblSoftwareTitles
WHERE Softwarename = @SoftwareName
AND SoftwareSystemType = @Softwaretype
);
Ambos métodos son susceptibles a una condición de carrera , así que si bien todavía usaría uno de los anteriores para insertar, pero puede proteger inserciones duplicadas con una restricción única:
CREATE UNIQUE NONCLUSTERED INDEX UQ_tblSoftwareTitles_Softwarename_SoftwareSystemType
ON tblSoftwareTitles (SoftwareName, SoftwareSystemType);
APÉNDICE
En SQL Server 2008 o posterior, puede usar MERGE con HOLDLOCK para eliminar la posibilidad de una condición de carrera (que aún no es un sustituto de una restricción única).
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);
Hay una gran solución para este problema, puede usar la palabra clave Merge de 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);
Puedes comprobarlo antes de seguir, a continuación se muestra la muestra.
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);
Más de un enlace de comentarios para una lectura adicional sugerida ... Aquí se puede encontrar un artículo de blog realmente bueno que evalúa varias formas de realizar esta tarea.
Usan algunas técnicas: "Insertar donde no existe", declaración "Combinar", "Insertar excepto" y su "combinación izquierda" típica para ver qué camino es el más rápido para realizar esta tarea.
El código de ejemplo utilizado para cada técnica es el siguiente (copiar / pegar directamente desde su página):
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
¡Es una buena lectura para aquellos que buscan velocidad! En SQL 2014, el método Insertar-Excepto resultó ser el más rápido para 50 millones de registros o más.
Esto no es una respuesta. Solo quiero mostrar que IF NOT EXISTS(...) INSERT método IF NOT EXISTS(...) INSERT no es seguro. Tienes que ejecutar la primera Session #1 y luego la Session #2 . Después de v #2 verá que sin un índice UNIQUE podría obtener pares duplicados (SoftwareName,SoftwareSystemType) . La demora de la sesión # 1 se usa para darle tiempo suficiente para ejecutar el segundo script (sesión # 2). Podrías reducir este retraso.
Sesión # 1 (SSMS> Nueva consulta> F5 (Ejecutar))
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;
Sesión # 2 (SSMS> Nueva consulta> F5 (Ejecutar))
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 s.SoftwareName=@SoftwareName
AND s.SoftwareSystemType=@SoftwareSystemType)
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;
Resultados:
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)