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

Question

Hi,

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.

Answer 1

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.

Answer 2

Use the Import and Export tool in SQL Server. Should be under programs -> SQL Server 2005 -> Import and Export (32) & Import and Export (64)