?
Solved

insert multiple rows into MSSQL from csv file

Posted on 2010-11-10
6
Medium Priority
?
622 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
6 Comments
 
LVL 7

Accepted Solution

by:
mkobrin earned 1000 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 81

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
Independent Software Vendors: 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 1000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

839 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