Oct 13, 2015

Import text file using xp_cmdshell in SQL Server



There are several technique to import file in SQL Server from external source like SSIS, BULK INSERT command, Import/Export wizard, OPENROWSET etc. one of them xp_cmdshell is use to import text file to SQL Server.

Below script can be use to import text file to SQL Server.
Note: Before using below script we have to enable xp_cmdshell once.


CREATE TABLE #TEMP
(
    Result  VARCHAR(MAX)
)

DECLARE @path VARCHAR(8000)
DECLARE @Query INT

-- read from text file
SET @path = 'TYPE D:\Test.txt'

INSERT INTO #TEMP
EXEC @Query = master.dbo.xp_cmdshell @path


SELECT  *
FROM    #TEMP
GO

DROP TABLE #TEMP