Inputting A Text File Into SQL Server Using T-SQL

Using BULK INSERT to Load a Text File into SQL Server

When you need to bring a large amount of structured data from a flat file into a SQL Server table, the most straightforward T‑SQL solution is BULK INSERT. It lets you read a file line by line and insert each row into a table without leaving the database. The command is built into SQL Server, runs entirely within a transaction, and can be scheduled through SQL Agent or incorporated into a larger script. Below is a practical walkthrough that covers everything from file preparation to handling common pitfalls.

Start by making sure the file is accessible from the SQL Server instance. If you’re running SQL Server locally, a file in a shared folder that the SQL Server service account can read will work. For a Windows deployment, place the file on a drive that the service account can reach, such as a UNC path (\\server\share\file.txt). If you’re using a cloud‑based instance or a managed service, you may need to upload the file to a storage account or use a local copy on the VM that hosts SQL Server.

Next, create a destination table that matches the file’s schema. For a simple comma‑separated file with three columns - ID (int), Name (varchar), and Score (decimal) - the table definition could look like this:

Notice that we didn’t add an identity column or set any constraints. BULK INSERT can work against tables with identity columns, but you’ll need to specify KEEPIDENTITY if you want to preserve the incoming values instead of letting SQL Server generate new ones.

The heart of the process is the BULK INSERT statement. Below is a basic example that reads a tab‑delimited file, treats the first line as data, and uses a simple field terminator. The example includes a handful of the most commonly used options:

Each option does a specific job:

• FIELDTERMINATOR tells SQL Server what character separates columns. For comma‑separated files, you’d use ','; for pipe‑delimited data, '|'
• ROWTERMINATOR marks the end of a row. Windows line endings are '\r ', Unix are ' ', and Mac (pre‑OS X) are '\r'
• FIRSTROW skips header lines. If your file contains a header row, set FIRSTROW = 2
• CODEPAGE ensures characters are interpreted correctly. 'ACP' uses the Windows code page, 'OEM' uses the console code page, 'RAW' keeps the raw bytes, or you can specify a specific code page number.
• DATAFILETYPE chooses between 'char' (standard text), 'native' (binary native format), 'widechar' (Unicode text), or 'widenative' (Unicode binary). Most flat files are 'char' or 'widechar'
• TABLOCK speeds up the operation by acquiring a bulk update lock on the table. It’s safe if no other users are updating the table simultaneously.