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 ?
alter table #fileinfo add fname varchar(255)
update #filename
set fname=@filename
where fname is null
update #filename
set fname=@filename
where fname is null
ASKER
That will work for the first loop maybe, but only the first.
-- 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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
update #filename
set fname=@filename
where fname is null