Link to home
Start Free TrialLog in
Avatar of elliottbenzle
elliottbenzle

asked on

insert multiple rows into MSSQL from csv file

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.
ASKER CERTIFIED SOLUTION
Avatar of mkobrin
mkobrin

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of arnold
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, to do that, In import export wizard have to set mapping and set customerID as identity.