We can populate a table using a dump of data available as a text file. You can also import data from various type of files including CSV and raw file into SQL. In the example we’ll look at how to import data from a Text file into SQL Table.

For this example, I have a Text file ProductList.txt as my input data.

We use a BULK INSERT statement to import data from a text file. If I need to import data to a Table called ProductList in the database, the table must exist in the database. For simple BULK INSERTs the input data must match the data types and number of columns in the target table. I have created a Table using the same structure as the INPUT file:

— © 2011 – Vishal (http://SqlAndMe.com)
CREATE TABLE [dbo].[ProductList]
(
      [ProductID] INT IDENTITY(1,1) NOT NULL,
      [Name] NVARCHAR(50) NOT NULL,
      [ProductNumber] NVARCHAR(50) NOT NULL,
      [StandardCost] MONEY NOT NULL
)

Once, the table is created, you can use BULK INSERT to import data from the text file as below:

— © 2011 – Vishal (http://SqlAndMe.com)
BULK INSERT ProductList
FROM 'C:\ProductList.txt'
WITH
(
      FIELDTERMINATOR =',',
      ROWTERMINATOR = '\n'
)

The ROWTERMINATOR and FIELDTERMINATOR are what they say. for example, if you are using any other symbol for separating columns then, you need to specify that as FIELDTERMINATOR. Same way for ROWTERMINATOR.

The output of the above statement will be:

(11 row(s) affected)

There are other options available for BULK INSERT, For a full list of options refer BULK INSERT (Transact-SQL).

Hope This Helps! Cheers!