SQL Server: How to Load Files From Given Directory To a Table

Recently a reader asked that how he can read his image files names, which are placed in a folder of operating system and how can he insert these images from this path, directly into a database table.

Following is the script, which will help you to read a file name from operating system, and later on insert these files one by one in a database table. For following script I have placed my target picture files at D:\ SamplePictures.
(Note: How to enable xp_cmdshell CLICK HERE)

Extended stored procedure xp_cmdshell will enable us to execute execute a shell command like DIR

USE AdventureWorks
GO 
 
IF OBJECT_ID('MyPictures', 'U') IS NOT NULL 
BEGIN
DROP TABLE dbo.MyPictures 
END
CREATE TABLE dbo.MyPictures
(
PicId INT IDENTITY(1, 1),
PicName VARCHAR(50),
OrignalFileName VARCHAR(50),
CreationDate DATE,
Picture VARBINARY(MAX)
) 
 
DECLARE @PathName VARCHAR(256), -- to hold folder path where pictures are placed
@CMD VARCHAR(512), -- to hold command, we will use DIR (Directory) as command
@SQLstr VARCHAR(1000)-- to hold generic query text 
 
-- Temp table to hold intermediate results
CREATE TABLE #CmdShell ( Title VARCHAR(512) ) 
SET @PathName = 'D:\SamplePictures\*.*' 
SET @CMD = 'DIR ' + @PathName + ' /TC' 
 
-- Populate temp table by reading file names,through xp_cmdshell, from given folder
INSERT  INTO #CmdShell
EXEC MASTER..xp_cmdshell @CMD 
 
-- Delete records other then file names
DELETE  FROM #CmdShell
WHERE   Title NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Title LIKE '%<DIR>%'
OR Title IS NULL 
 
-- Cursor is used to insert records, with picture file 
DECLARE CUR_IMG CURSOR FAST_FORWARD
FOR SELECT  Title AS OrignalFileName,
REVERSE(LEFT(REVERSE(Title),
CHARINDEX(' ', REVERSE(Title)) - 1)) AS FileName,
LEFT(Title, 10) AS CreationDate
FROM    #CmdShell        
OPEN CUR_IMG 
DECLARE @OrignalFileName VARCHAR(50),
@FileName VARCHAR(50),
@CreationDate DATETIME
FETCH NEXT FROM CUR_IMG INTO @OrignalFileName, @FileName, @CreationDate 
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT  @SQLstr = 'INSERT INTO dbo.MyPictures (PicName,OrignalFileName,CreationDate,Picture)
SELECT ''' + @OrignalFileName + ''',
''' + @FileName + ''',
'''
+ CAST(@CreationDate AS VARCHAR(50))
+ ''', 
* 
FROM OPENROWSET(BULK ''D:\SamplePictures\'
+ @FileName + ''', SINGLE_BLOB) AS imagesource'
EXEC ( @SQLstr
)
FETCH NEXT FROM CUR_IMG INTO @OrignalFileName, @FileName,
@CreationDate 
END
CLOSE CUR_IMG
DEALLOCATE CUR_IMG
GO 
 
-- drop temporary table when not required
DROP TABLE #CmdShell

SQL Server: What is an Algebrizer ?

 After an interview for DBA post, a friend of mine called me and without saying “Hello”, asked “What is Algebrizer?”  Yeah it’s the guy who is always ready to work for you when ever you write a query and press F5 to execute ;)

Algebrizer is a process in query execution, which starts its work after Parser. Once the parser finds query syntax correct, it passes it to the algebrizer. Here start work of algebrizer:

·     Algebrizer is responsible for objects and columns names (which you have provided in query or which are being referenced by your query) verification. For example if wrong name for a table is typed in a query, then its algebrizer’s duty to verify and generate an error.

·     Algebrizer also identifies all data types which are being processed for a given query.

·     Algebrizer verify that GROUP BY and aggregate columns are placed in right place or not. For example if you write following query and just Ctrl+F5 (just to parse). No error will be generated. But when you will press F5 (execute it). After Parsing, algebrizer will work and return an error.

USE AdventureWorks

GO

SELECT  MakeFlag,SUM(ListPrice)

FROM Production.Product

GROUP BY ProductNumber