How to import data from .txt file to populate a table in SQL Server

Question

Every day a PPE.txt file with clients data, separated by semicolon and always with the same layout is stored to a specific file directory.

Every day someone has to update a specific table from our database based in this PPE.txt.

I want to automate this process via a SQL script

What I thought would be a solution is to import the data via a script from this .txt file into a created table, then execute the update.

What I have so far is

IF EXISTS (SELECT 1 FROM Sysobjects WHERE name LIKE 'CX_PPEList_TMP%')
   DROP TABLE CX_PPEList_TMP
GO

CREATE TABLE CX_PPEList_TMP  
(
    Type_Registy CHAR(1),
    Number_Person INTEGER,
    CPF_CNPJ VARCHAR(14),
    Type_Person CHAR(1),
    Name_Person VARCHAR(80),
    Name_Agency VARCHAR(40),
    Name_Office VARCHAR(40),
    Number_Title_Related INTEGER,
    Name_Title_Related VARCHAR(80)
)

UPDATE Table1
SET SN_Policaly_Exposed = 'Y'
WHERE Table1.CD_Personal_Number = CX_PPEList_TMP.CPF_CNPJ
  AND Table1.SN_Policaly_Exposed = 'N'

UPDATE Table1
SET SN_Policaly_Exposed = 'N'
WHERE Table1.CD_Personal_Number NOT IN (SELECT CX_PPEList_TMP.CPF_CNPJ 
                                        FROM CX_PPEList_TMP)
  AND Table1.SN_Policaly_Exposed = 'Y'

I know I haven't given much, but it is because I don't have much yet.

I want to populate the CX_PEPList_TMP temp table with the data from the PEP.txt file via a script so I could just execute this script to update my database. But I don't know any kind of command I can use neither have found in my research.

Thanks in advance!

Solution 1

Using OPENROWSET

You can read text files using OPENROWSET option (first you have to enable adhoc queries)

Using Microsoft Text Driver

SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\Docs\csv\;',
'SELECT * FROM PPE.txt')

Using OLEDB provider

SELECT 
    * 
FROM 
OPENROWSET
        ('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\Docs\csv\;IMEX=1;','SELECT * 
FROM PPE.txt') t

Using BULK INSERT

You can import text file data to a staging table and update data from it:

BULK INSERT dbo.StagingTable
FROM 'C:\PPE.txt'
WITH 
  (
    FIELDTERMINATOR = ';', 
    ROWTERMINATOR = '\n' 
  )

Solution 2

In your case,i recommend you to use an ETL like SSIS it's much better and easy to work with and you can also Schedule the package to execute in a specific time