How to Check If Data Exists Before Writing to Table

Fastest way to determine if record exists

SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.

How to check if a specific data exist in a text file before insert it to SQL database table?

Based on your issue, you would like to insert the empId from the file to database if the

empId is not in database.

You can use the following code to get it.

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            List<Employee> list = new List<Employee>();
            var result = File.ReadAllLines("D:\\new.txt");
            string[] s = { "," };
            foreach (var item in result)
            {
                string[] arr = item.Split(s, StringSplitOptions.RemoveEmptyEntries);
                list.Add(new Employee { EmpId = Convert.ToInt32(arr[0]), FirstName = arr[1], LastName = arr[2] });
            }
            foreach (Employee item in list)
            {
                bool t = DoesEmployeeExist(item.EmpId);
                if(t==true)
                {
                    MessageBox.Show("Database already has the record "+item.EmpId);
                }
                else
                {
                    string sql = string.Format("insert into Employees (empID,FirstName,LastName)values({0},'{1}','{2}')", item.EmpId, item.FirstName, item.LastName);
                    string connstring = @"";
                    SqlConnection connection = new SqlConnection(connstring);
                    connection.Open();
                    SqlCommand command = new SqlCommand(sql, connection);
                    command.ExecuteNonQuery();
                    connection.Close();

                }
            }
            MessageBox.Show("Test");
        }

        public bool DoesEmployeeExist(int empId)
        {

            try
            {
                string sql = "Select empID from Employees where empID = '" + empId + "'";
                string connstring = @"";
                SqlConnection connection = new SqlConnection(connstring);
                connection.Open();
                SqlCommand command = new SqlCommand(sql,connection);
                SqlDataReader dr = command.ExecuteReader();
                if(dr.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch (Exception)
            {
                throw;
            }


        }
    }
    public class Employee
    {
     public int EmpId { get; set; }

     public string FirstName { get; set; }

     public string LastName { get; set; }

    }

The next is my txt file:

The next is my database record:

Query to check if record exists and also column value in table is null

Based on your clarifications in the comments I think you want to test for the following 2 conditions:

Condition 1: User record exists but user_id is null

select 1
from UserAccount UA
inner join Contact as C on UA.email = C.email and C.[type] = 'Director'
where C.InvId = @InvId
-- User Record exists
and exists (select 1 from Users U where U.email = UA.email)
-- user_id is null
and UA.[user_id] is null

Condition 2: User record doesn't exist

select 1
from UserAccount UA
inner join Contact as C on UA.email = C.email and C.[type] = 'Director'
where C.InvId = @InvId
-- User Record doesn't exist
and not exists (select 1 from Users U where U.email = UA.email)

These can be combined with an OR to test both conditions as follows:

declare @userExists int;

if exists (
  select 1
  from UserAccount UA
  inner join Contact as C on UA.email = C.email and C.[type] = 'Director'
  where C.InvId = @InvId
  and (
    (UA.[user_id] is null and exists (select 1 from Users U where U.email = UA.email))
    or not exists (select 1 from Users U where U.email = UA.email)
  )
)
begin
  set @userExists = 1;
end;

As an aside should the variable really be called @userExists? Isn't it @userNotExists?

Note 1: It would be worth knowing what you then use the variable @userExists for - for example if you use it to update a record then most likely the update and test can be combined into a single statement.

Note 2: I have to assume this is only for test purposes? Because joining on email address isn't a recommended practise - unless you are prepared to put a unique constraint on email address, but some people still do share an email address.

Note 3: Its best practice to be consistent in your table naming as to whether you use the singular or plural form. You've used Users (plural) and Contact (singular).

SQL: How to properly check if a record exists

It's better to use either of the following:

-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;

The first alternative should give you no result or one result, the second count should be zero or one.

How old is the documentation you're using? Although you've read good advice, most query optimizers in recent RDBMS's optimize SELECT COUNT(*) anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.

Check if data exists (avoid duplication) in database before running some SQL Server code

This works by carrying out a separate check for duplicate data for each table:

INSERT INTO  Match    
SELECT *
FROM #temp1
EXCEPT
SELECT CompetitionID, DateKickOff, TimeKickOff, TeamIDHome, TeamIDAway, ScoreHome, ScoreAway
FROM Match

DELETE #CSVTest_Data
  FROM #CSVTest_Data d
WHERE EXISTS( SELECT * from Data d2 WHERE
    d.FirstTimeTaken = d2.OddsFirstTimeTaken AND
    d.LatestTimeTaken = d2.OddsLastTimeTaken AND
    d.Market = d2.MarketName AND
    d.Outcome = d2.Outcome AND
    d.Odds = d2.Odds AND
    d.NumberOfBets = d2.NumberOfBets AND
    d.VolumeMatched = d2.VolumeMatched AND
    d.InPlay = d2.InPlay)

--Add MatchID column to Temp Data file and fill it with the most recent match ID
ALTER TABLE #CSVTest_Data ADD MatchID INT
update #CSVTest_Data
     Set MatchID = (SELECT TOP 1 MatchID FROM BetfairFootballDB..Match
                   ORDER BY MatchID DESC)

INSERT INTO  BetfairFootballDB..Data (MatchID, OddsFirstTimeTaken, OddsLastTimeTaken, MarketName, Outcome, Odds, NumberOfBets, VolumeMatched, InPlay)
SELECT MatchID, FirstTimeTaken, LatestTimeTaken, Market, Outcome, Odds, NumberOfBets, VolumeMatched, InPlay
FROM #CSVTest_Data

SQL - How to check if data exists on all available dates?

You can use aggregation with a having clause:

select username
from t
where date >= '2021-04-01' and date < '2021-04-04'
group by username
having count(*) = 3;   -- user name appears on all three days

Note that this returns the username. If you actually want the details rows, use this in a query using a join or similar construct to get the original rows.

---

Related Topics