Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create a new table and import data from csv file into SQL Server 2005

Tags:

import

csv

I have several files about 15k each of CSV data I need to import into SQL Server 2005.

What would be the simplest way to import the csv data into sql server? Ideally, the tool or method would create the table as well, since there are about 180 fields in it, this would simplify things.

like image 791
BumbleBee Avatar asked May 13 '10 22:05

BumbleBee


People also ask

How do I import a CSV file into SQL Server automatically?

1.1 In Object Explorer, right-click the database you want to import data and select Data Pump -> Import Data. 1.2 In the wizard that opens, select the file format to be imported—CSV in our case—and continue customizing the import process. Once done, click Save Template.


1 Answers

BULK INSERT is your friend. Something like:

BULK INSERT MyTable 
    FROM 'c:\data.csv' 
    WITH 
    ( 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

EDIT

Although BULK INSERT will not create the table for you. You could look at using SQL Server Integration Services, which will infer the schema from the data file. Take a look at http://www.kodyaz.com/articles/import-csv-flat-file-into-sql-server-using-ssis-integration-services.aspx as an example.

like image 170
Paul Kearney - pk Avatar answered Sep 24 '22 20:09

Paul Kearney - pk