SQL – Bulk Insert – Import Data From Flat File

 

The following Bulk Insert command Imports data from a file called FOO_DATA.csv into an SQL table called FOO_DATA.

Firstly, the Table FOO_DATA needs to cleared of its data:

TRUNCATE TABLE orders.dbo.FOO_DATA

The truncate table command deletes all data within a table and cannot be undone (Unless you can restore from backup), so use carefully. Table formats , indexes etc will remain intact.

A simple Bulk Insert command is as follows:

BULK INSERT orders.dbo.FOO_DATA

from ‘C:\reports\FOO_DATA.csv’
WITH ( FIELDTERMINATOR = ‘,’ )

The table FOO_DATA and the file FOO_DATA.CSV must be in the same field format. In this example FOO_DATA.csv is a comma separated file with no quotes.

You can import from a remote location by changing the file location:

BULK INSERT orders.dbo.FOO_DATA

from ‘\\ROB-PC\files\FOO_DATA.CSV’
WITH ( FIELDTERMINATOR = ‘,’ )

The account under which the SQL Services run must have access to this location.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s