Import Text File Using XP_CMDSHELL In SQL Server

In this post we will learn how to Import Text File Using XP_CMDSHELL In SQL Server.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.

TSQL:

------ 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:\1.txt'
 
INSERT INTO #TempOutput
EXEC @rCode = master.dbo.xp_cmdshell @sqlCommand
 
-- Display results--
SELECT  *
FROM    #TempOutput
GO
 
-- Drop temporary table--
DROP TABLE #TempOutput