Comparing Excel Data to Database Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing Excel Data to Database Field

Author

Topic

djpin
Starting Member

27 Posts

Posted - 2014-08-04 : 15:37:59

Hi,

I have an excel spreadsheet that only has email addresses in a single columnar format on it (318 emails). I want to check and see if any of those emails are in the database. Is there a easier way than having to enter 300+ "OR" statements? How do I do this?

SELECT "Name"."FIRST_NAME", "Name"."LAST_NAME", "Name"."EMAIL", "Name"."ID", "Name"."MEMBER_TYPE"
FROM "APSCU_PROD"."dbo"."Name" "Name"
WHERE Name.EMAIL='marie@bahoo.com' OR Name.EMAIL='markg@ts.com' OR Name.EMAIL='mare@t.edu'

Thanks,

DJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-04 : 15:40:34

Import the data into a staging table using SSIS/bcp/etc and then use T-SQL to check which exist:

select *
from yourtable t
where exists (select * from yourstagingtable s where t.email = s.email)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

djpin
Starting Member

27 Posts

Posted - 2014-08-04 : 17:01:14

I'm in sinking sand ... LOL ... Thought there would be an easier way.

Not sure how to import the data into a staging table using SSIS/bcp/etc.

Thanks for your suggestion though.

Thanks,

DJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-04 : 17:23:12

SSMS:
create table Email_Stage (email varchar(1000))

Run this from a cmd window (Start..Run..cmd):
bcp DbName.dbo.Email_Stage in c:\email.xls -Sserver1\instance1 -T -c -t, -r\r\n

SSMS:
select *
from yourtable t
where exists (select * from EMail_Stage where t.email = s.email)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-04 : 17:23:46

Instead of bcp, you can right click on the database and use the import wizard. It'll create the table and import the data. Then you'd run my select query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

djpin
Starting Member

27 Posts

Posted - 2014-08-05 : 09:55:21

Outstanding!

Thanks!!!!!

Thanks,

DJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-05 : 12:20:39

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-08-06 : 07:22:24

OPENROWSET is another method of accessing data from EXCEL
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

djpin
Starting Member

27 Posts

Posted - 2014-08-06 : 14:16:20

Thanks for sharing.

Thanks,

DJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources