Import text file using xp_cmdshell in SQL Server




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

The below script can be used to import text file to SQL Server.
Note: Before using the 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


No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...