Bulk Inserting/Loading data from Flat files

Following are the possible ways in which one can bulk insert data from flat/text files into SQL Server.
1. Using Command line "bcp" utility
2. Using Transact-SQL "Bulk insert" command
3. Using SSIS/DTS packages

DTS packages have certain programmability limitations and don't always offer the best performance.

BULK INSERT gives optimal performance over DTS/SSIS packages.

BULK INSERT
----------------
Bulk insert doesn't create the table automatically. It expects the table to be existed already.

Format file structure














For more details on format file, visit the below page
http://msdn.microsoft.com/en-us/library/ms179250.aspx

Scenario 1: n - # columns in table, m - #columns in text file (1:1)
assume n=m = 3
Then format file

9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100      "\t"    2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Scenario 2:
n>m, To skip a table column
n=3, m=2

Approach 1:

9.0
2
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Notice that row for "Col2" is missing.

Approach 2:

9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       0        ""      0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

For more details on skipping a database column, visit the below page
http://msdn.microsoft.com/en-us/library/ms179250.aspx

Scenario 3:
n<m, 9.0 4 1 SQLCHAR 0 7 "," 1 col1 "" 2 SQLCHAR 0 100 "," 0 ExtraField SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 100 "," 2 col2 SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 100 "\r\n" 3 col3 SQL_Latin1_General_CP1_CI_AS For more details on skipping a data filed, visit the below page http://msdn.microsoft.com/en-us/library/ms187908.aspx  


Scenario 4: n=m=4 , order of the table columns is not the same as data fields

9.0
4
1       SQLCHAR       0       100     ","     3     Col3               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","     2     Col2               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       7       ","     1     Col1               ""
4       SQLCHAR       0       100     "\r\n"  4     Col4               SQL_Latin1_General_CP1_CI_AS

For more information on this scenario, visit the below page
http://msdn.microsoft.com/en-us/library/ms191175.aspx