SQL Server – Import text file using xp_cmdshell
There are several options available to import data from external sources to SQL Server. Such as Import & Export Wizard, BULK INSERT command, SSIS and OPENROWSET.
Apart from this options you can also use xp_cmdshell to import text file to SQL Server. We need to utilize dos command TYPE for this purpose. Below script can be used to import a text file to database.
-- Script to import text file using xp_cmdshell -- Create Temporary table to store data CREATE TABLE #TempOutput ( Result VARCHAR(MAX) ) DECLARE @sqlCommand VARCHAR(1000) DECLARE @rCode INT -- read from text file SET @sqlCommand = 'TYPE C:\Vishal.txt' INSERT INTO #TempOutput EXEC @rCode = master.dbo.xp_cmdshell @sqlCommand -- display results SELECT * FROM #TempOutput GO -- drop temporary table DROP TABLE #TempOutput -- Script End
Above script requires xp_cmdshell to be enabled on server.
Hope This Helps!