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:
Things you need
- MS Sql Server Database (example: trackedatabase)
- Sql Server Table (example: trackingdata)
- .CSV File (example: morenames.csv)
- Administrative rights
Select your database using the following code (replacing the sample name with your database name):
Write the Bulk Insert statement to insert .csv file data:
(FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK)
Test your results:
SELECT * FROM trackingdata
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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for