Solved

insert multiple rows into MSSQL from csv file

Posted on 2010-11-10
6
600 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 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 77

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now