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
(
ResultVARCHAR(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

If you like this post, do like my Facebook Page -> SqlAndMe

EMail me your questions -> Vishal@SqlAndMe.com

Follow me on Twitter -> @SqlAndMe

Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data