How to Select From a CSV File in SQL

Written by linda wyndom
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Select From a CSV File in SQL
CSV to SQL (database on paper image by .shock from Fotolia.com)

Transferring tuples of data between two different database systems usually happens through automation, with pre-written routines (or scripts) that execute during off-peak hours. Occasionally, you'll need to manually input information, typically from a retired or incompatible system. These one-time exception requests are generally not worth the effort to set up in automated scripts. You can import (and export) to the tables in your database through generic, comma-separated value (.csv) files using the bcp.exe utility command included with MS Sql Server or using the Bulk Insert statement, which these steps address, or OpenRowSet statement.

Skill level:
Easy

Other People Are Reading

Things you need

  • MS Sql Server Database (example: trackedatabase)
  • Sql Server Table (example: trackingdata)
  • .CSV File (example: morenames.csv)
  • Administrative rights

Show MoreHide

Instructions

  1. 1

    Select your database using the following code (replacing the sample name with your database name):

    USE trackedatabase

    GO

  2. 2

    Write the Bulk Insert statement to insert .csv file data:

    BULK

    INSERT trackingdata

    FROM 'c:\morenames.csv'

    WITH

    (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK)

    GO

  3. 3

    Test your results:

    SELECT * FROM trackingdata

    GO

Tips and warnings

  • "In SQL Server 2005 and later versions, BULK INSERT enforces new data validation and data checks that could cause existing scripts to fail when they are executed on invalid data in a data file," according to Microsoft Developers Network.
  • Enclose your path to the .csv file with single quotation marks [']. Step 2 uses 'c:\,' the root directory of a PC, only as an example, your path may include other drives or directories.
  • Consider using TABLOCK even though the process will work without it. Always have your entire table in single user mode when making modifications to more than one row.
  • You can use other one-off methods to import text data to a MS Sql Server database, including techniques such as importing the .csv first to MS Excel and then uploading the .xls(x) file to MS Sql Server. Sometimes for a one-time import you'll find this easier than going through the MS Sql Server Administration panel just to upload a few rows of data.
  • Try something new on a test system first.
  • Make a complete backup of the database before attempting to import any type of new data, whether you are using BCP or Bulk Insert or OpenRowSet.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.