Scripts for performing an insert if not exists operation

samuelkaranja · samuelkaranja · commit bff95704ac05 · 2024-09-11T23:17:12.000+03:00
diff --git a/sql-queries/insert-if-not-exists/insert-if-not-exists-mysql.sql b/sql-queries/insert-if-not-exists/insert-if-not-exists-mysql.sql
@@ -0,0 +1,9 @@
+-- Using INSERT IGNORE
+INSERT IGNORE INTO table_name (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
+VALUES (id_value, name_value, national_id_value, birth_date_value, enrollment_date_value, graduation_date_value, gpa_value);
+
+
+-- Using INSERT … ON DUPLICATE KEY UPDATE
+INSERT INTO table_name (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
+VALUES (id_value, name_value, national_id_value, birth_date_value, enrollment_date_value, graduation_date_value, gpa_value)
+ON DUPLICATE KEY UPDATE id = id;
diff --git a/sql-queries/insert-if-not-exists/insert-if-not-exists-postgresql.sql b/sql-queries/insert-if-not-exists/insert-if-not-exists-postgresql.sql
@@ -0,0 +1,17 @@
+-- Using INSERT … ON CONFLICT
+INSERT INTO table_name (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
+VALUES (id_value, name_value, national_id_value, birth_date_value, enrollment_date_value, graduation_date_value, gpa_value)
+ON CONFLICT (id) DO NOTHING;
+
+-- Using a DO Block
+DO $$
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1 FROM table_name WHERE id = id_value
+    ) THEN
+        INSERT INTO table_name (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
+        VALUES (id_value, name_value, national_id_value, birth_date_value, enrollment_date_value, graduation_date_value, gpa_value);
+    ELSE
+        RAISE NOTICE 'A student already exists with this id. No insertion performed!';
+    END IF;
+END $$;
diff --git a/sql-queries/insert-if-not-exists/insert-if-not-exists-sqlserver.sql b/sql-queries/insert-if-not-exists/insert-if-not-exists-sqlserver.sql
@@ -0,0 +1,16 @@
+-- Using MERGE
+MERGE INTO table_name AS target
+USING (VALUES (id_value, name_value, national_id_value, birth_date_value, enrollment_date_value, graduation_date_value, gpa_value))
+       AS source (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
+ON target.id = source.id
+WHEN NOT MATCHED BY TARGET THEN
+    INSERT (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
+    VALUES (source.id, source.name, source.national_id, source.birth_date, source.enrollment_date, source.graduation_date, source.gpa);
+
+-- Using EXCEPT
+INSERT INTO table_name (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
+SELECT id_value, name_value, national_id_value, birth_date_value, enrollment_date_value, graduation_date_value, gpa_value
+EXCEPT
+SELECT id, name, national_id, birth_date, enrollment_date, graduation_date, gpa
+FROM table_name
+WHERE id = id_value;
