How do I import data from a.txt file to populate tables in SQLServer

Using OPENROWSET

You can read text files using the OPENROWSET option (first you must enable adhoc queries)

Use the Microsoft text driver

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

Use the OLEDB provider

SELECT * FROM OPENROWSET (' microsoft.ace.oledb.12.0 ','Text; Database=C:\Docs\csv\; IMEX=1; ','SELECT * FROM PPE.txt') tCopy the code

Using BULK INSERT

You can import text file data into temporary tables and update data from them:

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