The Structured Query Language's INSERT statement adds new records to database tables. In some business situations, duplicates pose problems, especially for master tables such as customers, products or vendors. For example, you don't want the same customer number assigned to two different customers, or for the same customer to occur twice. SQL databases offer many strategies for preventing duplicates, such as checking for them and running an UPDATE statement if the program finds a record. Because SQL dialects for Oracle and Microsoft SQL Server differ slightly, checking for duplicates in each requires its own coding methods.
Microsoft SQL Server
Video of the Day
Step 1
Start your favorite SQL coding utility, such as SQL Server Management Studio.
Video of the Day
Step 2
Type an IF EXISTS statement using the following code as a guide:
IF EXISTS (SELECT table_key FROM table where table_key ='key_value') UPDATE table SET first_field='first value', second_field='second value' WHERE table_key = 'key_value' ELSE INSERT INTO table (table_key, first_field, second_field) VALUES ('key_value','first value','second value')
In this example, the IF EXISTS statement tests for a duplicate record in the table called "table." If the test is true, it executes the UPDATE statement which replaces values in the existing record. If no duplicate exists, the ELSE clause performs an INSERT, adding the record into the table. The actual SQL code for your application may look like the following:
IF EXISTS (SELECT customer_number FROM customers where customer_number ='00001000') UPDATE customers SET name='SAM JONES', address='500 MAIN ST.' WHERE customer_number = '00001000' ELSE INSERT INTO customers (customer_number,name,address) VALUES ('00001000','SAM JONES','500 MAIN ST.')
Step 3
Execute the code by clicking the "Play" button.
Oracle
Step 1
Start the SQL*Plus utility.
Step 2
Enter PL/SQL code using the following as a guide:
DECLARE duplicate_record INTEGER; BEGIN SELECT COUNT(*) INTO duplicate_record FROM table WHERE table_key = 'key_value' AND ROWNUM = 1; IF duplicate_record = 1 THEN UPDATE table SET first_field='first value', second_field='second value' WHERE table_key = 'key_value'; ELSE INSERT INTO table (table_key, first_field, second_field) VALUES ('key_value','first value','second value') END IF; END;
Note the use of the PL/SQL ROWNUM pseudocolumn in the above code. This ensures that if the test finds many duplicates, the "duplicate_record" variable contains at most a "1." The IF statement performs an UPDATE on the table if duplicates exist; otherwise it does a standard INSERT, adding a new record. After you substitute your database's table and field names into the code, it may resemble the following SQL program:
DECLARE duplicate_record INTEGER; BEGIN SELECT COUNT(*) INTO duplicate_record FROM customers WHERE customer_number = '00001000' AND ROWNUM = 1; IF duplicate_record = 1 THEN UPDATE customers SET name='SAM JONES', address='500 MAIN ST.' WHERE customer_number = '00001000'; ELSE INSERT INTO customers (customer_number, name, address) VALUES ('00001000','SAM JONES','500 MAIN ST.') END IF; END;
Step 3
Press "Enter" to execute the command.