Get List Of Files From a Windows Directory to SQL Server

In SQL Server, we read data from single text file; excel file…etc. However we can extend this to read all the files in a particular directory. This post demonstrates a part of this scenario. Here we will discuss how to get the list of files from a particular directory, then can be extended to load them into SQL Server, which is not the scope of this post.

So in order to use xp_cmdshell, we need to enable it as it is disabled by default.

Here is the way to enable:

1 2 3 4 5 6 7 8 9 10 11 12

--allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO --Update the currently configured value for advanced options. RECONFIGURE GO --Enable XP_CMDSHELL EXEC sp_configure 'xp_cmdshell', 1 GO --Update the currently configured value for this feature. RECONFIGURE GO

Refer Image-1 for the list of files in the directory. Now create a table and load the file list into it:

(Image-1)

1 2 3 4 5 6 7 8 9 10 11 12 13

--Create the table to store file list CREATE TABLE myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256)) --Insert file list from directory to SQL Server DECLARE @Path varchar(256) = 'dir C:\Import\' DECLARE @Command varchar(1024) =  @Path + ' /A-D  /B' INSERT INTO myFileList EXEC MASTER.dbo.xp_cmdshell @Command --Check the list SELECT * FROM myFileList GO --Clean up DROP TABLE myFileList GO

Here is the result in the table.

How to execute generated script(.sql file) with schema and data in SQL Server 2008

SQL Server offers 2 command prompt features that can se used for executing large queries - osql (will be removed in future), and sqlcmd

osql is located in the Tools\Binn subfolder. To execute a SQL script:

1. Start the Command Prompt
2. Navigate to the folder where the osql utility is located
3. Run the command in the following format:
   osql –H <workstation name> -S <server_name[\instance_name]> -U <user login ID> -P <login password> –i <full path to script>

To execute the large.sql file located in the D:\test, against the Central database on the SQL Server instance Dell\SQL2012, as an sa with the 'sqladmin' password, run the following command:

osql -H Dell -S Dell\SQL2012 -i D:\test\large.sql -U sa -P sqladmin

The sqlcmd command line utility is also located in the SQL Server’s Tools\Binn sub-directory. To execute a SQL script:

1. Start the Command Prompt
2. Navigate to the folder where the sqlcmd utility is located
3. Run a command in the following format:
   sqlcmd –S <server name> -d <database name> -i <full path to script> -U <user login ID> –P <login password>

To execute the same as above, run the following command:

sqlcmd -S Dell\SQL2012 -d Central -i D:\test\large.sql -U sa –P sqladmin

Start the sqlcmd Utility Run Transact-SQL Script Files Using sqlcmd