BULK INSERT to import data from file to SQL Server database

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Stored Procedures SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Stored procedures, triggers and SSRS reports Top tips and tricks for SQL Server database development Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure Archive: Reporting Services Tips for tuning SQL Server 2005 to improve reporting performance Manipulate column names in a SQL Server table Licensing a standby server for SQL Server replication SQL Server trigger vs. stored procedure to receive data notification Storing and searching for image files in SQL Server Can I encrypt and restore a database backup in SQL Server 2005? Using BULK INSERT to insert rows from SQL Server dataset to table SQL Server query to import database names Troubleshoot SQL Server 2005 SP2 installation error Workaround to Reporting Services error in SQL Server Express

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary library  (SearchSQLServer.com) trigger  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

There are many ways to import data into SQL Server and what you choose depends greatly on your specific scenario. If you want to simply load contents of a text file into a temporary table, the BULK INSERT command should do the trick. If the number and the order of the columns in the file is the same as in the destination table, your statement could be as simple as this:

BULK INSERT MyTable
FROM 'c:datamydata.txt'
WITH
(
FIELDTERMINATOR =' |',
ROWTERMINATOR =' |\n'
)

If you want to exclude some columns or modify the order of columns, create a format file and specify it as a parameter to BULK INSERT. You can refer to Books Online to learn more about how to use BULK INSERT and how to create a format file.