May 8, 2018 at 7:07 am
So I am fairly a beginner with SQL. I can write basic queries to get needed data, and I have created basic SSIS packages and stored procedures by pretty much copying other existing ones as frame work.
I just got put on a project where I am going to be getting a CSV file from a company that will have new applicant data. (SSN, Name, address, etc)
That CSV file will be imported into our current HR system. This will be easy for any one who is a new hire. My problem is what I will need to do if someone is a rehire and already exists in our system.
I will need some way to take that CSV file and see if the any of the SSN numbers already exists in our DB. (the HR system will allow the same SSN to be entered on a new user, we dont want that to happen)
I am a little lost on how I will be able to check and see if the SSN already exists. I am ok at pulling basic data, but pretty much a beginner at checking to see if data already exists such as the SSN.
Can anyone point me in the right direction??
May 8, 2018 at 7:58 am
gothaimviii - Tuesday, May 8, 2018 7:07 AMSo I am fairly a beginner with SQL. I can write basic queries to get needed data, and I have created basic SSIS packages and stored procedures by pretty much copying other existing ones as frame work.I just got put on a project where I am going to be getting a CSV file from a company that will have new applicant data. (SSN, Name, address, etc)
That CSV file will be imported into our current HR system. This will be easy for any one who is a new hire. My problem is what I will need to do if someone is a rehire and already exists in our system.I will need some way to take that CSV file and see if the any of the SSN numbers already exists in our DB. (the HR system will allow the same SSN to be entered on a new user, we dont want that to happen)
I am a little lost on how I will be able to check and see if the SSN already exists. I am ok at pulling basic data, but pretty much a beginner at checking to see if data already exists such as the SSN.
Can anyone point me in the right direction??
You would use the where clause to filter the data. If I want to find all apples in a table for fruits, I'd use something like where fruit type = 'Apple'.
Just like where ssn = '123-45-6789'. You would need to use quotes if the column you are searching on is a character column (like varchar, char, nvarchar, nchar, etc).
You can find examples in the documentation for the where clause:
WHERE (Transact-SQL)
Sue
May 8, 2018 at 8:27 am
This is a pretty basic thing (not saying anything about your skills so please don't take it that way).
You want to find new records. Look into the LEFT JOIN so you can match on the SSN and find the ones that don't already exist.
The other side of the coin is what do they want done with rehires? That's where a basic INNER JOIN will identify them and you can handle them according to the requirements. I would think they want something done with them if they're being moved from Terminated to Rehired/Active.
Hopefully this will give you some direction on how to write the queries you need.
May 8, 2018 at 8:32 am
Sue_H - Tuesday, May 8, 2018 7:58 AMgothaimviii - Tuesday, May 8, 2018 7:07 AMSo I am fairly a beginner with SQL. I can write basic queries to get needed data, and I have created basic SSIS packages and stored procedures by pretty much copying other existing ones as frame work.I just got put on a project where I am going to be getting a CSV file from a company that will have new applicant data. (SSN, Name, address, etc)
That CSV file will be imported into our current HR system. This will be easy for any one who is a new hire. My problem is what I will need to do if someone is a rehire and already exists in our system.I will need some way to take that CSV file and see if the any of the SSN numbers already exists in our DB. (the HR system will allow the same SSN to be entered on a new user, we dont want that to happen)
I am a little lost on how I will be able to check and see if the SSN already exists. I am ok at pulling basic data, but pretty much a beginner at checking to see if data already exists such as the SSN.
Can anyone point me in the right direction??
You would use the where clause to filter the data. If I want to find all apples in a table for fruits, I'd use something like where fruit type = 'Apple'.
Just like where ssn = '123-45-6789'. You would need to use quotes if the column you are searching on is a character column (like varchar, char, nvarchar, nchar, etc).
You can find examples in the documentation for the where clause:
WHERE (Transact-SQL)Sue
Hi Sue,
I know how to do that for individual SSN's, but not to take a file and compare to a DB table.
Appreciate the input. 🙂
May 8, 2018 at 8:37 am
You can do it with either SSIS or T-SQL (stored procedures).
First you need to import into a staging table using SSIS data flow or T-SQL bulk insert. For example and explanation on the later, check the following article: Skipping Columns (& other tricks) Using BCP Format Files (SQL Spackle) - SQLServerCentral
I'd would advise to reconsider the fact of storing SSN's and using them to match rows. I believe that there are some regulations against this.
May 8, 2018 at 8:40 am
gothaimviii - Tuesday, May 8, 2018 8:32 AMSue_H - Tuesday, May 8, 2018 7:58 AMgothaimviii - Tuesday, May 8, 2018 7:07 AMSo I am fairly a beginner with SQL. I can write basic queries to get needed data, and I have created basic SSIS packages and stored procedures by pretty much copying other existing ones as frame work.I just got put on a project where I am going to be getting a CSV file from a company that will have new applicant data. (SSN, Name, address, etc)
That CSV file will be imported into our current HR system. This will be easy for any one who is a new hire. My problem is what I will need to do if someone is a rehire and already exists in our system.I will need some way to take that CSV file and see if the any of the SSN numbers already exists in our DB. (the HR system will allow the same SSN to be entered on a new user, we dont want that to happen)
I am a little lost on how I will be able to check and see if the SSN already exists. I am ok at pulling basic data, but pretty much a beginner at checking to see if data already exists such as the SSN.
Can anyone point me in the right direction??
You would use the where clause to filter the data. If I want to find all apples in a table for fruits, I'd use something like where fruit type = 'Apple'.
Just like where ssn = '123-45-6789'. You would need to use quotes if the column you are searching on is a character column (like varchar, char, nvarchar, nchar, etc).
You can find examples in the documentation for the where clause:
WHERE (Transact-SQL)Sue
Hi Sue,
I know how to do that for individual SSN's, but not to take a file and compare to a DB table.
Appreciate the input. 🙂
There are a number of options, the easiest way if you're already comfortable loading the data into a table is to just load the new file into a staging table and compare the two tables in SQL. You can also do the same thing in SSIS.
May 8, 2018 at 8:40 am
gothaimviii - Tuesday, May 8, 2018 8:32 AMSue_H - Tuesday, May 8, 2018 7:58 AMgothaimviii - Tuesday, May 8, 2018 7:07 AMSo I am fairly a beginner with SQL. I can write basic queries to get needed data, and I have created basic SSIS packages and stored procedures by pretty much copying other existing ones as frame work.I just got put on a project where I am going to be getting a CSV file from a company that will have new applicant data. (SSN, Name, address, etc)
That CSV file will be imported into our current HR system. This will be easy for any one who is a new hire. My problem is what I will need to do if someone is a rehire and already exists in our system.I will need some way to take that CSV file and see if the any of the SSN numbers already exists in our DB. (the HR system will allow the same SSN to be entered on a new user, we dont want that to happen)
I am a little lost on how I will be able to check and see if the SSN already exists. I am ok at pulling basic data, but pretty much a beginner at checking to see if data already exists such as the SSN.
Can anyone point me in the right direction??
You would use the where clause to filter the data. If I want to find all apples in a table for fruits, I'd use something like where fruit type = 'Apple'.
Just like where ssn = '123-45-6789'. You would need to use quotes if the column you are searching on is a character column (like varchar, char, nvarchar, nchar, etc).
You can find examples in the documentation for the where clause:
WHERE (Transact-SQL)Sue
Hi Sue,
I know how to do that for individual SSN's, but not to take a file and compare to a DB table.
Appreciate the input. 🙂
If what you mean by file is that you have a group of SSNs, you can use EXISTS or IN in the where clause (or NOT EXISTS or NOT IN).
What are you going to do with this file? Are you importing it and working from there or what? Some of how you do things would depend on that file - what type of file, do you need to import it, are you just using it for an SSN reference, etc.
Sue
May 8, 2018 at 10:21 am
I would use SSIS to bring in the CSV file into a SQL table, as mentioned earlier. Then use SQL to compare the data.
You could look at the MERGE statement. In the MERGE you can tell it to handle data that matches one way and those that don't another.
Look here for and explanation of the MERGE statement: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply