Insert, cursor, sp


I'm inserting info from the sp getfiledetails into a temp table as below:
______________________________________________
OPEN cur
FETCH NEXT FROM cur INTO @Filename
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @Filename = @Directory + @Filename
     INSERT #FileInfo EXEC xp_getfiledetails @Filename
     FETCH NEXT FROM cur into @Filename
END
______________________________________________
This is working fine, however I want to add the info from the @Filename variable also into the #FileInfo table how can I do that ?
LVL 8
SNilssonAsked:
Who is Participating?
 
OlegPConnect With a Mentor Commented:
To #FileInfo  add  the following  IDR INT IDENTITY(1,1), DR varchar(250),FN varchar(500)


change
 INSERT #FileInfo EXEC xp_getfiledetails @Filename
to
 INSERT #FileInfo ([alternate name],.....,[Attributes]) --all list of fields
EXEC xp_getfiledetails @Filename
UPDATE  #FileInfo
SET DR = @Directory ,FN=@Filename
WHERE IDR=@@IDENTITY
0
 
Jay ToopsCommented:
alter table #filename add fname varchar(255)
update #filename
set fname=@filename
where fname is null
0
 
Jay ToopsCommented:
alter table #fileinfo add fname varchar(255)
update #filename
set fname=@filename
where fname is null
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
SNilssonAuthor Commented:

That will work for the first loop maybe, but only the first.
0
 
Jay ToopsCommented:
-- check to see if the field exists before adding
IF NOT EXISTS
   (SELECT c.* FROM syscolumns c   JOIN sysobjects o ON c.id = o.id
    WHERE o.name = '#fileinfo'  AND c.name = 'fname')
   BEGIN
      -- Field does not exist
      alter table #fileinfo add fname varchar(255)
   END

update #fileinfo
set fname=@filename
where fname is null

0
 
SNilssonAuthor Commented:

I used a identity column and then did an update on the column with the DOS filename in it, so points for OlegP for giving the idéa of using a identity column.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.