If the data in the file is separated with a delimiter, such as a comma
This tutorial assumes we have already created a table with columns firstname, lastname and position. First, create a text file where the first row is firstname, lastname, position, and there are additional rows that contain firstnames, lastnames and positions. Save this file as players.txt.

1. In SQL Server, expand Databases
2. Right click on the database being used, and select Tasks -> Import Data. A popup box will appear.
3. Select Next
4. Change Data Source to Flat File source
5. Click Browse and select players.txt -> Open
   • Because the data in our file is separated by a delimiter, a comma, we select delimited.
   • Because the first row of our text files has the column headers (firstname, lastname, position), we check mark Column names in the first data row
6. Select Next
7. The GUI should show the correct column headers (firstname, lastname, position), and the data should be matched to the appropriate header.
8. On the left, select Advanced. Hightlight OutputColumnWidth -> Suggest Types -> OK, and this will set the varchar to the longest string of data in the column. Repeat this for each column header.
9. Select Next.
10. Change Destination to SQL Server Native Client 11.0. Also, ensure the correct database is selected in the Database dropdown. Select Next.
11. Click Edit Mappings, and map the source to the destination. Click OK.
12. Click Next.
13. Click Next.
14. Click Finish.

SQL Server Import Wizard: If the data in the file is NOT separated with a delimiter, such as a comma
This tutorial is based off the pbSmartPostage MUST dump text files. Save the most recent MUST dump text file to your desktop.

TXT file preparation

1. Go to www.example.com and save the lastest TXT file to your desktop
2. Rename the txt file to: MUSTdump
   
   SQL Server
3. In SQL Server, running the following command: delete from MUSTdump; This removes all of the previous entries.
4. In SQL Server, expand Databases
5. Right click on the pbSmartPostage database, and select Tasks -> Import Data. A popup box will appear.
6. Select Next
7. Change Data Source to Flat File source
8. Click Browse and select dump.txt -> Open
   • Because the data in our file is not separated by a delimiter, a comma, we select fixed width
   • Because the first row of our text files has the column headers (firstname, lastname, position), we check mark Column names in the first data row
9. On the left, select Columns. Drag the red dot to the end of what makes a row, or key in the row width. For the MUST dump, the row width is 258.
10. Still in Columns, just to the left of each row header, left click to add a black dot separator. For example, left click at width 83 to add a separator.
    • 81 USER_EMAIL
    • 164 USER_ROLE
    • 180 BPN_ID
    • 191 CAN_ID
    • 203 MATERIAL_ID
    • 228 STATUS
    • 239 SUB_ORDER
11. Preview should verify the structure is correct
12. Select Next.
13. Change Destination to SQL Server Native Client 11.0. Also, ensure the correct database is selected in the Database dropdown. Select Next.
14. Click Edit Mappings, and map the source to the destination. Also, if this is the first time performing this import, Create destination table should be bulleted. Every other import should have Append rows to the destination table bulletted. Click OK.
15. Click Next.
16. Click Next.
17. Click Finish.

SQL Job
The Express (free) edition of SQL Server Management Studio does not have the ability to create jobs. At minimum, the Developer ($60 one-time fee) version of SQL Server Management Studio must be used to create jobs.

How to import a flat file using BCP

1. In this example, the database name is AdventureWorks2012. There is a table named myTestFormatFiles, and the table has 4 columns:
   • Col1
   • Col2
   • Col3
   • Col4
2. There is a flat file C:\\myTestFormatFiles-c.txt, where the data is separated by commas, like this:
    

   10,Field2,Field3,Field4
   15,Field2,Field3,Field4
   46,Field2,Field3,Field4
   58,Field2,Field3,Field4

   
    
3. BCP uses a Format Files that is used to interpret the Flat File. To create the Format File, enter this in a Command Line prompt: bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T. This will create the file myTestFormatFiles.Fmt. Lets move the myTestFormatFiles.Fmt to C:\\myTestFormatFiles.Fmt.
4. Now, to perform the insert, enter this command: bcp AdventureWorks2012..myTestFormatFiles in C:\\myTestFormatFiles-c.txt -f C:\\myTestFormatFiles.Fmt -T. Now, if we look in SQL Server, we will see the data has been added to the table.