Save file's name into a table

Question

Post reply

Save file's name into a table

adonetok

SSCrazy Eights

Points: 8395
More actions
March 2, 2016 at 7:10 am

#325212

There are about 5000 jpg files stored in a file server in which the length of file's name is the same.
For example, in server order \\order\2016\
20160101.jpg
20160110.jpg
...
20160228.jpg
How to code to store file's name 20160101, 20160110...20160228 into a #order?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Several ways to do this. Probably the easiest is something like this:

INSERT INTO #order

EXEC xp_cmdshell 'dir /b \\order\2016'

John

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

adonetok (3/2/2016)
There are about 5000 jpg files stored in a file server in which the length of file's name is the same.
For example, in server order \\order\2016
20160101.jpg
20160110.jpg
...
20160228.jpg
How to code to store file's name 20160101, 20160110...20160228 into a #order?

The task cannot be completed without enabling a feature on your SQL Server that allows it to communicate outside the engine. Easy options are SQLCLR and xp_cmdshell but there are others too. I prefer SQLCLR over xp_cmdshell for various reasons we do not need to go into.

Are those enabled on your instance or do you control the instance and can enable them?

If unsure, to check:

select name,

case value when 1 then 'enabled' else 'disabled' end as is_enabled,

case value_in_use when 1 then 'true' else 'false' end as is_in_use

from sys.configurations

where name in ('clr enabled', 'xp_cmdshell')

order by name;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

When I try script below but got an error said: Invalid object name '#order'

select * from #order

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

adonetok (3/2/2016)
When I try script below but got an error said: Invalid object name '#order'
select * from #order

Lookup xp_cmdshell in Books Online to see the definition of the resultset it returns and create a temporary table named #order that mimics it before running the INSERT...EXEC statement.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

adonetok SSCrazy Eights Points: 8395 More actions · Answer 5

Sorry, I realize that should create a temp table first.

After that, another error.

It seems to me IT will not allow me using sys.xp_cmdshell

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

adonetok (3/2/2016)
Invalid object name '#order'

That's the name of the table you gave in your original post.

John

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

With the normal warnings about using undocumented features (which I frequently do, BTW), the following code will do what you ask and enable you to do more that you might not be telling us. Most everyone has privs to use xp_dirtree and doesn't require any special settings to be enabled. The 1st operand must be the file path and can be a drive:path specification or a UNC specification. The second operand (a "1") is depth control saying to only traverse the current directory identified in the first operand. The 3rd operand (also a "1") is what most folks don't know about and is necessary to return file names. Without it, you only get directory names. Other details are in the code below.

--===== Create the receiver table

CREATE TABLE #Order

(

ObjectName VARCHAR(500) NOT NULL --Will be a filename where IsFile = 1

,Depth TINYINT NOT NULL --Will always be "1" for the commmand below

,IsFile TINYINT NOT NULL --1 for files, 0 for directories

,FileOrder INT NULL --Will indicate the sort order for processing files in name order

)

;

--===== Get the directory and file names for the file path.

-- Obviously, you need to change 'yourfilepath' to the correct file path.

INSERT INTO #Order

(ObjectName, Depth, IsFile)

EXEC xp_dirtree 'C:\',1,1

;

--===== Add a sequence to the file rows for processing files in name order.

-- If you don't actually need to do any processing, you don't actually need this step.

WITH cteEnumerate AS

(

SELECT ObjectName

,IsFile

,FileOrder

,RowNum = ROW_NUMBER() OVER (ORDER BY ObjectName)

FROM #Order

WHERE IsFile = 1

)

UPDATE cteEnumerate

SET FileOrder = RowNum

;

--===== Display just the files in filename order

SELECT [FileName] = ObjectName

,FileOrder

FROM #Order

WHERE IsFile = 1

ORDER BY [FileName]

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

adonetok SSCrazy Eights Points: 8395 More actions · Answer 8

Got an error said

Windowed functions can only appear in the SELECT or ORDER BY clauses

After comment out

--UPDATE #Order

-- SET FileOrder = ROW_NUMBER() OVER (ORDER BY ObjectName)

-- WHERE IsFile = 1

It works like magic.

Thank you so much.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 9

adonetok (3/2/2016)
Got an error said
Windowed functions can only appear in the SELECT or ORDER BY clauses
After comment out
--UPDATE #Order
-- SET FileOrder = ROW_NUMBER() OVER (ORDER BY ObjectName)
-- WHERE IsFile = 1
It works like magic.
Thank you so much.

My bad. I didn't test it and forgot about the Windowing functions not being able to be used in an UPDATE. I'll update the code and repost it above in a minute or two.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 10

The code has been updated, tested, and reposted. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

adonetok SSCrazy Eights Points: 8395 More actions · Answer 11

adonetok

SSCrazy Eights

Points: 8395

March 2, 2016 at 8:26 am

#1862409

Did you post new code?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

adonetok (3/2/2016)
Did you post new code?

Jeff Edited his original post.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

Jeff Moden (3/2/2016)
With the normal warnings about using undocumented features (which I frequently do, BTW), the following code will do what you ask and enable you to do more that you might not be telling us.

I rarely consider the undocumented procs. In this case however, without enabling features, xp_dirtree may end being the best option for the OP.

The other caveat besides xp_dirtree being undocumented, and same goes with the options I mentioned, is that the service account running SQL Server must have permission to read the directory being referenced.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato