Solved

insert multiple rows into MSSQL from csv file

Posted on 2010-11-10
6
610 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

717 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