This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing the demonstration on how to read the data from the file to SQL Server.
Using xp_cmdshell windows command shell stored procedure, we can do data operation in the windows file directories.
I also shared an article on how to export SQL Server table data into text file.
SQL Server: xp_cmdshell and BCP to export table data in a Text File
Enable xp_cmdshell:
|
1 2 3 4 5 6 7 8 9 10 |
USE master; GO sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE 'xp_cmdshell', 1 GO RECONFIGURE GO |
Read the data from the file:
I created a text file in ‘D:\dbrnd.txt’ location. My file contains “Database Research & Development”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @Command VARCHAR(255) DECLARE @Filepath VARCHAR(255) SELECT @Filepath = ISNULL(@Filepath,'D:\dbrnd.txt') PRINT 'Path: ' + @Filepath CREATE TABLE #tbl_ReadFile(filerecord VARCHAR(MAX)) IF @Filepath IS NOT NULL AND LEN(@Filepath) > 10 BEGIN SELECT @Command = 'type ' + @Filepath INSERT INTO [dbo].[#tbl_ReadFile] EXEC master.dbo.xp_cmdshell @Command END |
Result:
|
1 2 3 4 5 |
SELECT *FROM #tbl_ReadFile filerecord ---------------------------------- Database Research & Development |
Leave a Reply