Importing data from a text file

In this recipe, we will import some pipe-delimited files into an existing table in our SQL Server instance.

Getting ready

In this recipe, we will create an empty table called SampleText in your database instance:

CREATE TABLE [dbo].[SampleText]
(
  [CustomerID] [varchar](20) NOT NULL PRIMARY KEY,
  [LastName] [varchar](50) NOT NULL,
  [FirstName] [varchar](50) NOT NULL,
  [Phone] [char](20) NOT NULL,
  [AddressLine] [varchar](50) NULL,
  [City] [varchar](20) NULL,
  [State] [char](5) NULL,
  [Country] [varchar](10) NOT NULL
)

We will import records in the file called Customers.txt into the SampleText table. The text file is a pipe-delimited text file that contains values for all the columns in the SampleText table:

How to do it...

The following steps will import a text file into a SQL Server table:

1. Open PowerShell ISE as an administrator.
2. Import the SQLPS module as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script...