While this is part 5 of this series, this will be the most simple example. I will be importing data from a tab-delimited text file into a table called Names in a SQL Server database called MikeTest. To do this I will be using SQL Server Management Studio. The table will have two columns and the text file will only have two rows. I will be using the BULK INSERT feature of T-SQL. In the real world, things will be more complicated and larger, and the thing that you will be spending your time on is the data types of the columns. It’s important to get that right.

Below is a simple create table statement that you could use in your non-production environment. First, create a testing database called MikeTest (or whatever pleases you; just change the code below to reflect your database name).

USE [MikeTest]
GO
CREATE TABLE [dbo].[Names](
	[Name] [varchar](50) NULL,
	[Number] [int] NULL
) ON [PRIMARY]
GO

Below is our BULK INSERT Statement. I have a text file already created and located in the directory shown.

USE MikeTest;  
GO  
BULK INSERT Names FROM 'D:\MyData\Test\importSQL.txt'  
   WITH (  
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = '\t',  
      ROWTERMINATOR = '\n'  
);  
GO 

Since this is a tab-separated value file, you might want to name the file with a .tsv extension instead of the txt file extension shown here. The text file could look something like this below.

Bob     7
Sally Smith  2

If we run a SELECT in SSMS, we get the following

This example above imports a tab-delimited file. What if the file was comma-separated (CSV)? Just change the field terminator as shown in the line below.

FIELDTERMINATOR = ',', 

The BULK INSERT will add rows to the bottom of the table. That means that you can run it multiple times without an error and the data in your file will be added multiple times.