SQL Server provides four methods you can use when you need to insert large quantities of data into a SQL Server database. These automated techniques will help you insert data from the results of a SQL query, a text file or another database. The four techniques discussed in this article are:
- Copying data between tables with INSERT INTO
- Importing data from text files with BULK INSERT
- Copying data from the command line with bcp
- Importing data with SQL Server Integration Services
Inserting Query Results
The first method of inserting bulk data into a table is using the INSERT INTO statement. This allows you to import the results of another SQL statement and is useful for copying full or partial tables. Here's an example that copies students in the class of 2009 from a student table into an alumni table:
INSERT INTO alumni
SELECT * FROM students
WHERE class_year = '1998'
When using the INSERT INTO statement, you must either copy between tables with identical table structures or manipulate the data so that the columns match up correctly.
Using BULK INSERT
The BULK INSERT command allows you to import data from a text file. For example, the following Transact-SQL statement allows you to read a text file from your computer and insert the contents into a SQL Server table:
BULK INSERT classes
FROM 'C:\classes.txt'
Using bcp
The bulk copy (bcp) command of Microsoft SQL Server provides you with the ability to insert large numbers of records directly from the command line. In addition to being a great tool for command-line aficionados, bcp is a powerful tool for those seeking to insert data into a SQL Server database from within a batch file or other programmatic method. This powerful command has a number of options available. For full documentation, read the article
Importing and Exporting SQL Server Data from the Command Line with bcp.
SQL Server Integration Services (SSIS)
SQL Server Integration Services (SSIS) allows you to import and export data from SQL Server databases using an easy-to-navigate graphical interface. It replaces the Data Transformation Services (DTS) found in earlier versions of SQL Server. For more information, read the article
Importing Data with SSIS.
