Solved

insert multiple rows into MSSQL from csv file

Posted on 2010-11-10
6
606 Views
Last Modified: 2012-05-10
Hi, I'm using MS SQL server management studio express. I have a table that looks like this:

ID, cust_name, cust_email, cust, cust_bday, cust_aniversary

I also have a csv file which only contains a list of email addresses, it looks like this:

a455@hughes.net 
aabeson@yahoo.com 
abean1313@aol.com 
abischoff@columbus.rr.com 
abradbee@kent.edu 
acyoung@ymail.com 

I would like to get these into the database and only need them to go into the cust_email column, the other columns can be blank (of course id will auto populate).

What is the best way to do this? Thanks.
0
Comment
Question by:elliottbenzle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 7

Accepted Solution

by:
mkobrin earned 250 total points
ID: 34109087
SQL Server has a data Import wizard that will help you do this.

Right click on your data base, then select all tasks, then select import data, and follow the wizard. There will be options for you to match fields, ignore fields etc. Pretty easy to use.

Regards,

Mike
0
 
LVL 78

Expert Comment

by:arnold
ID: 34109152
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

load data infile [filename] into table [tablename] (cust_email)

The items in [] mean that they need to be replaced with the correct information.
note that if you have any column that is defined as not null and does not have a default defined, the attempt will fail.

A similar option available in MS SQL:
http://msdn.microsoft.com/en-us/library/ms188365.aspx

bulk insert requires that all columns be present in the CSV since there is no way to designate the column to which the data belongs without using a format file.
http://msdn.microsoft.com/en-us/library/ms178129.aspx or using an alternative mechanism of using insert in conjunction with bulk or other tools that would convert the CSV file for use with the insert.
0
 
LVL 7

Expert Comment

by:rashmi_vaghela
ID: 34109221
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Assisted Solution

by:AmmarR
AmmarR earned 250 total points
ID: 34109348
Just as mkobrin: mentioned

use the SQL import wizard, chose your csv file as datasource and your sql table as destination
and click on Edit mapping button, you will get the Columns mapping window, select only the cust_email column
and continue your wizard

check screen shots
Map.png
Output.png
0
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34109561
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34110043
Yes, to do that, In import export wizard have to set mapping and set customerID as identity.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question