bluk insert

Create a table with the following structure

1.create table bulk_insert_test

2.(

3.    employee_id int identity(1,1),

4.    first_name varchar(30), 

5.    last_name varchar(30),

6.    address varchar(100)

7.)

Note that the table has two extra columns employee_id and address

Now this query will fail

1.BULK INSERT bulk_insert_test

2.   FROM 'g:\test.txt'

3.   WITH

4.     (

5.        FIELDTERMINATOR =',',

6.        ROWTERMINATOR = '\n'

7.      )

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

1.create view vw_bulk_insert_test

2.as

3.select first_name,last_name from bulk_insert_test

 

 

Now use BULK INSERT using this view

1.BULK INSERT vw_bulk_insert_test

2.   FROM 'g:\test.txt'

3.   WITH

4.     (

5.        FIELDTERMINATOR =',',

6.        ROWTERMINATOR = '\n'

7.      )

Name it as format.txt Now use BULK INSERT using this format file

1.BULK INSERT bulk_insert_test

2.   FROM 'g:\test.txt'

3.with (formatfile = 'g:\format.txt')

3 Use OPENROWSET 

1.INSERT INTO bulk_insert_test(first_name,last_name)

2.SELECT 

3.    * 

4.FROM 

5.    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