BULK INSERT is used to insert data to a table from a text file.
Only problem with it is that it is not possible to specify the columns that you want data to be imported.
For example the table has 4 columns whereas there are data for 2 columns in the text file.
Consider the following data
Source data in text file called test.txt (It has first_name and last_name)
Davolio, Nancy
Fuller, Andrew
Leverling, Janet
Peacock, Margaret
Buchanan, Steven
Suyama, Michael
King, Robert
Callahan, Laura
Dodsworth, Anne
Create a table with the following structure
create table bulk_insert_test
(
employee_id int identity(1,1),
first_name varchar(30),
last_name varchar(30),
address varchar(100)
)
Note that the table has two extra columns employee_id and address
Now this query will fail
BULK INSERT bulk_insert_test
FROM 'g:\test.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n'
)
Becuase the table has four columns and text file has data for only two columns. In this case you can import data to specific columns using the following methods
1 Use a View
Create a view that has only required columns
create view vw_bulk_insert_test
as
select first_name,last_name from bulk_insert_test
Now use BULK INSERT using this view
BULK INSERT vw_bulk_insert_test
FROM 'g:\test.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n'
)
2 Create Format file
8.0
2
1 SQLCHAR 0 30 "," 2 first_name SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 30 "," 3 last_name SQL_Latin1_General_Cp437_BIN
Name it as format.txt Now use BULK INSERT using this format file
BULK INSERT bulk_insert_test
FROM 'g:\test.txt'
with (formatfile = 'g:\format.txt')
3 Use OPENROWSET
INSERT INTO bulk_insert_test(first_name,last_name)
SELECT
*
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=g:\', test#txt)
As you see method 3 doesn't require any extra work if the number of columns are different