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.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...