Please help me with sql statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help me with sql statement

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-08-11 : 07:16:46

I have a table (one field - called 'dir_output') which holds information in the format:-

Volume in drive C is System
Volume Serial Number is FG56-7289
NULL
Directory of C:\mydriver\pt119179
NULL
26/07/2007 19:22 <DIR> .
26/07/2007 19:22 <DIR> ..
25/03/2007 21:35 11,677 dellbp.cat
26/07/2007 19:22 8,168 INFCACHE.1
25/03/2007 21:35 11,327 oemsetup.inf
26/07/2007 19:22 20,912 oemsetup.PNF
25/03/2007 21:35 4,136 readme.txt
5 File(s) 56,220 bytes
2 Dir(s) 10,286,813,696 bytes free
NULL
Volume in drive C is System
Volume Serial Number is FG56-7289
NULL
Directory of C:\drivers\pt789802
NULL
26/07/2007 19:22 <DIR> .
26/07/2007 19:22 <DIR> ..
25/03/2007 21:35 10,699 5000xzvp.cat
25/03/2007 21:35 12,020 5000xzvp.inf
26/07/2007 19:22 23,680 5000xzvp.PNF
25/03/2007 21:35 9,757 865.cat
25/03/2007 21:35 4,787 865.inf
26/07/2007 19:22 8,996 865.PNF

etc......

I am trying to copy part of the info into another table, the info I need is 'File Name', 'Create Date' and 'Directory Name'

I can get the first two easily enough using the following SQL:-


INSERT INTO DirectoryContents1 ([File_Name], [Create_Time])

     SELECT   SUBSTRING(dir_output,37,(LEN(dir_output)-36)) AS [File_Name]
    , CONVERT(datetime,SUBSTRING(dir_output,1,17),103) AS [Create_Time]
    , @dirlongnm AS [dirname]
      FROM  DirectoryContents2
     WHERE  (SUBSTRING(dir_output,1,1) <> ' '
	   AND (IsNumeric(SUBSTRING(dir_output, 1, 2))=1)
	   AND (IsNumeric(SUBSTRING(dir_output, 16, 1))=1)
       AND  SUBSTRING(dir_output,22,5) <> '<DIR>')
go

but how can I get the Directory Name to be read out for each file in each directory?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-08-12 : 02:07:29

30 views and no comments yet?
Is there no one who can help?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-12 : 02:53:25

At first: how to ensure the data is coming up in the posted order?

And then it looks like a job for a curser.
Here comes a little approach on how it can be done:

open cursor
start loop
if entry contains dirname --> take dirname to variable @dirname
if entry contains fileinformation --> insert desttable extracted filename, extracted create_time, @dirname
fetch next
next loop

Webfred

Too Old to Rock 'n' Roll, Too Young to Die

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-08-12 : 05:52:22

Look here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108623

and you can see how I've progressed to date, but now I'm stuck!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 06:41:56

[code]CREATE TABLE #Stage
(
RecID INT IDENTITY(1, 1),
Raw CHAR(600),
Size BIGINT,
CreateTime DATETIME,
Dir VARCHAR(400)
)

CREATE UNIQUE CLUSTERED INDEX IX_Stage ON #Stage (RecID)

INSERT #Stage
(
Raw
)
EXEC xp_cmdshell 'dir c:\*.* /s /-c /4 /tc'

UPDATE #Stage
SET Size = CONVERT(BIGINT, SUBSTRING(Raw, 18, 18)),
CreateTime = CONVERT(DATETIME, SUBSTRING(Raw, 1, 17))
WHERE Raw LIKE '[0-9]%'
AND Raw NOT LIKE '%<DIR>%'

DECLARE @Dir VARCHAR(400)

UPDATE s
SET @Dir = Dir = CASE
WHEN Raw LIKE ' Directory of %' THEN SUBSTRING(Raw, 15, 400)
ELSE @Dir
END
FROM #Stage AS s WITH (INDEX (IX_Stage))

UPDATE #Stage
SET Dir = RTRIM(Dir) + '\'
WHERE RIGHT(Dir, 1) <> '\'

SELECT Dir,
RTRIM(SUBSTRING(Raw, 38, 600)) AS FileName,
Size,
CreateTime
FROM #Stage
WHERE CreateTime IS NOT NULL
ORDER BY RecID

DROP TABLE #Stage[/code]

E 12°55'05.25"
N 56°04'39.16"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-08-12 : 07:52:25

Thanks for that, much neater than my version!
I'll give it a shot now.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-08-12 : 10:45:21

Sorry - getting errors:-

(328 row(s) affected)
Msg 242, Level 16, State 3, Line 18
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Thanks anyway, I'll try and debug it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 11:14:13

Try putting a

SET DATEFORMAT {mdy | ymd | dmy}

before the UPDATE #Stage part.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 11:15:43

[code]CREATE TABLE #Stage
(
RecID INT IDENTITY(1, 1),
Raw CHAR(600),
Size BIGINT,
CreateTime DATETIME,
Dir VARCHAR(400)
)

CREATE UNIQUE CLUSTERED INDEX IX_Stage ON #Stage (RecID)

INSERT #Stage
(
Raw
)
EXEC xp_cmdshell 'dir c:\temp\*.* /s /-c /4 /tc'

SET DATEFORMAT YMD

UPDATE #Stage
SET Size = CONVERT(BIGINT, SUBSTRING(Raw, 18, 18)),
CreateTime = CONVERT(DATETIME, SUBSTRING(Raw, 1, 17))
WHERE Raw LIKE '[0-9]%'
AND Raw NOT LIKE '%<DIR>%'

DECLARE @Dir VARCHAR(400)

UPDATE s
SET @Dir = Dir = CASE
WHEN Raw LIKE ' Directory of %' THEN SUBSTRING(Raw, 15, 400)
ELSE @Dir
END
FROM #Stage AS s WITH (INDEX (IX_Stage))

UPDATE #Stage
SET Dir = RTRIM(Dir) + '\'
WHERE RIGHT(Dir, 1) <> '\'

SELECT Dir,
RTRIM(SUBSTRING(Raw, 37, 600)) AS FileName,
Size,
CreateTime
FROM #Stage
WHERE CreateTime IS NOT NULL
ORDER BY RecID

DROP TABLE #Stage[/code]

E 12°55'05.25"
N 56°04'39.16"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-12 : 17:40:16

Peso
Patron Saint of Lost Yaks

I TAKE A BOW!

That is cool.

Webfred

Too Old to Rock 'n' Roll, Too Young to Die

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-08-13 : 01:29:36

Thankyou VERY much, that has saved me a lot of time, I worked on my (inadequate) script for about thre days and you have 'knocked' one up in a fraction of the time.
Big Thumbs up m8!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 02:35:18

Thank you and good luck.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources