Jump Start: Loading Data with BULK INSERT

T-SQL's BULK INSERT statement lets you load data into a SQL Server database. BULK INSERT is especially useful in SQL Server Express, which lacks the SQL Server Integration Services data import and export subsystem that's available in the Standard and Enterprise editions of SQL Server.

Being a T-SQL tool, BULK INSERT doesn't require any external programs and is easy to incorporate into your T-SQL batches. You can also use BULK INSERT within a T-SQL transaction, and doing so would let you roll back all the data inserted using the BULK INSERT statement. This capability can provide useful protection against data corruption.

Let's assume we have a comma-separated value (CSV) file named dataimport.csv that's in the C:\temp directory and contains the following sample data:

1,DataOne,20080317
2,DataTwo,20080317
3,DataThree,20080317
4,DataFour,20080317

To import the data in this file using the T-SQL BULK IMPORT statement, we could use the following code:

Use MyDB  GO
CREATE TABLE MyTable
  (
  	MyCol1 INT,
  	MyCol2 VARCHAR(50),
  	MyCol3 SMALLDATETIME
   )
BULK INSERT MyDB.dbo.MyTable
      FROM 'c:\temp\dataimport.csv'
      WITH
           (
              FIELDTERMINATOR = ',',
              ROWTERMINATOR = '\n'
           )
SELECT * FROM MyTable

This code creates a table named MyTable to store the imported values. The table's data types must match the data that's being imported; if they don't, SQL Server Express will generate an error.

The BULK INSERT statement identifies the table into which the data will be imported, the file containing the data to be imported (in this case C:\temp\dataimport.csv), and the field separator value and end-of-line characters that the file uses--my sample file uses a comma and a \n, respectively. You can adjust these values depending on the format of the import file.

Keep in mind that running BULK INSERT under Windows Vista requires elevated (i.e., Administrator) privileges

Comments

Plain text