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!
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe