Archive

Posts Tagged ‘sql server import text file’

SQL Server – Import text file using xp_cmdshell

29.07.2013 No comments

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!

Vishal