Databases
StackOverflow2013
Postsdbo
POCheck for duplicates based on two columns
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCheck for duplicates based on two columns
    primarykey
    data
    text
    <p>So I have an employee table as follows:</p> <pre><code>empid | companyid | empnum | ... 1 | 1 | 1 | ... 2 | 1 | 2 | ... 3 | 1 | 3 | ... 4 | 2 | 1 | ... 5 | 2 | 2 | ... </code></pre> <p>Every employee entry on the table gets a unique ID (empid) that is generated automatically by MSSQL. I have a PHP application that takes a CSV file as input with a "master list" of employees. However that CSV file only has the columns from "empnum" right-ward (it does NOT contain companyid, or obviously employeeid). companyid is added when I iterate through each line of the CSV file. The CSV <em>should</em> only contain employees that are not in the database yet, but there is no guarantee, so I need to check to make sure the employee is not there before inserting. The verification method is to make sure that for this particular companyid (let's say we're inserting for companyid = 1), there is not already a matching empnum in the table. So if I had a CSV file with this data, it would be valid:</p> <pre><code>empnum, ... 4, ... </code></pre> <p>However if I had a CSV file with this data, it would NOT be valid:</p> <pre><code>empnum, ... 3, ... </code></pre> <p>Since 3 already exists as an empnum for companyid=1, the ENTIRE import should fail. In the PHP application, I have:</p> <pre><code>try { db-&gt;beginTransaction(); while (($data = fgetcsv($handle, 5000, ",")) !== FALSE) { //SQL TO INSERT ROW } db-&gt;commit(); } catch (PDO Exception) { db-&gt;rollBack(); } </code></pre> <p>What SQL should I use to insert the row so that it will cause an exception if an empnum already exists for that companyid? What database changes would I need to make? My initial thoughts are:</p> <p>Option 1 - Insert into a temporary table, then run a select query that joins the two tables on matching values in companyid/empnum, if it returns rows > 0, throw an exception. PRO: Only 1 select. CON: Everything is inserted to the temp table THEN dumped into real table IF it passes select = lots of wasted time if it fails</p> <p>Option 2- Do a select statement based on the current row, if it returns rows > 0, throw an exception, otherwise insert into real table. PRO: You catch failures as soon as they happen so you can potentially save yourself time. CON: Your queries have now increased by (2x-1) so you have a lot of extra overhead if you succeed!</p> <p>So my questions would be: - What's the best option (1, 2, or something else entirely)? - Can I somehow get MSSQL to throw the exception when inserting?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USD.R.
    1. USD.R.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCheck for duplicates based on two columns
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POCheck for duplicates based on two columns
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis isn't the exact code but you can get an idea: $nextEmpnum=("SELECT MAX empnum FROM employee_table WHERE companyid=".$company_id)+1 "INSERT INTO employee_table values (".$nextEmpnum.",".$company_id
      singulars
      1. POCheck for duplicates based on two columns
      1. USDavid Starkey
    2. COSorry...I should have specified better. I'm not trying to CREATE a unique id for the empnum. empnum will be supplied by the company that's importing the data. I simply need to see if the employee already exists on the table (based on a combination of companyid and empnum)
      singulars
      1. POCheck for duplicates based on two columns
      1. USD.R.
    3. COAlso, not sure why this question got a downvote. If you downvoted, please say why!
      singulars
      1. POCheck for duplicates based on two columns
      1. USD.R.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload